Historic Trend (Update)

[BGedco2022]

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 ^_^

[sbeadle]

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]

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.

[geoffpatton]

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'