Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
79159members
343629posts

Historic Trend (Update)

BGedco2022
Lieutenant JG
Lieutenant JG
0 Likes
3
128

Historic Trend (Update)

Hello Everyone,

 

We are trying to display a trend using average method but it retrieves inaccurate avg. value

For example:\

Raw Historic = {200, 680, 0, 0, 68, 987, 0}

totalCount = 7

then the avg = sum/totalCount = 276.4

 

but the actualCount = 4 not 7

therefore the actual avg = 483.75

 

Is there a way to custom the average method in case eliminating each value that equal to zero from the avg. calculation?

 

*****Update******

We used user query to calculate the average without zero values for specific ID

SELECT 
AVG( "ValueAsReal" ) AS AVRG
FROM
CDBHISTORIC
WHERE
"Id" = [POINT ID] AND "RecordTime" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' }
AND ValueAsReal <> 0

 

Now, we do not know how to apply the same query in a template (to pass the [POINT ID] to the query)

 

Grateful for any hint ^_^

 

 

3 Replies 3
sbeadle
Janeway Janeway
Janeway
0 Likes
0
113

Re: Historic Trend (Update)

Hi.

Yes, a zero is still a number, so Geo SCADA is calculating correctly!

Some points:

a) Maybe the point should not report a value if zero? Depends on the device data source and driver config?

b) What is the data quality of the values which are zero? (Display a Historic List) Queries and Aggregate calcs can filter by quality.

c) How do you want the result output - maybe just on the screen, or to a point on the server as a calculation? 

BevanWeiss
Spock
Spock
0 Likes
0
107

Re: Historic Trend (Update)

As @sbeadle mentioned, 0 is a number, and you likely have it with a 'Good' data quality also.

 

If you can tag it as bad quality in your outstation then it will be nicer.  Then I believe the default historic algorithm settings will remove it from the calculation (if not, then you could just untick the bad quality option on the algorithm).

 

If you can't do this in the outstation, then you will likely have to use something like a Calculation Point, or some Logic and an Internal Point to achieve the same thing, and re-write the values to either ignore the 0 values, or to give them a bad quality.

 

Using the SQL query vs an OPC algorithm likely won't give you the same performance.  Not an issue for small point counts and low existing server load, but it is a scalability concern longer term.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
geoffpatton
Commander
Commander
0 Likes
0
99

Re: Historic Trend (Update)

IF like Bevan mentioned this is only ever going to be a small amount of data and you are wanting to put this in an embedded list you can reference the point ID so it works from in a instance of a template. After you enter your initial query right Click on the query and select Generate Animation and it puts the query in the SQL animation of the embedded list. This will put the query in the Animations and then you can edit to be able to retrieve the ID by inserting it in like using a combination of ' and + with a normal property reference, you can have multiple references.

 

I think your SQL animation will need to be like this, but I almost always have to try it a few times before I get it right.

 

'SELECT AVG( "ValueAsReal" ) AS AVRG FROM CDBHISTORIC WHERE "Id" = ''' + "....ID" + ''' AND "RecordTime" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' } AND ValueAsReal <> 0'