Hi,

I am having a problem calculating the totalized value of a flow meter using

datalink. My guess is that my problem is probably caused because the end time

is referencing a future time.

What I am trying to do is make a report that gives the running totalized

value between the "start of shift" to the "end of shift."

Elsewhere in my report I have used the method of start time = start of

shift date and time, and end time = end of shift date and time (even though

this is a future time). This method works for minimum, maximum, count, and

average but it does not seem to work for "Total." For example, if I

use this method for minimum, when I open the report, every time I recalculate the

function, it will give me the minimum as of the current time...(hopefully I am

making sense)

For whatever reason, when I reference a future time, it doesn't just give me

the total up until "now" but instead it generates some random

total value of which I cannot determine the source or the values used

to produce this total.

I have tried 2 different methods for the "Calculated Data"

function, 1) using the tag name of the flow meter as an expression:

'tagnamehere' and then using the TOTAL option for the calculated mode

(time-weighted, compressed); 2) using the flowmeter referenced as a data item

using the same options as method 1.

Both of these methods produce matching and "correct" results if I

change the "end time" to be less than or equal to the current time,

however, if the end time goes beyond the current time into the

future, the value begins to increase as the time difference

increases.

I have used a third, "brute force" method of calculating the total

by using the raw compressed data function between the two shift times. I then

calculate the differential time and multiply by the flow rate over that time

period, then add all of those up. This is the method that I am using to check

the other two methods against.

Is there something that I can do to make the "calculated data"

function work properly? Where is it getting the random values from when the end

time is a future value? I have attached a picture of the excel data.

P.S. The flow meter unit of measurement is Gallons per Minute so the

conversion factor is 1440.

EDIT: I guess I should note, the way that I would like this spreadsheet to work is the following: at the beginning of the shift, the total should be 0 gallons. As the shift progresses, the total gallons should be updated in real time (every time the spreadsheet is calculated) until the end of shift. At the end of shift, that value will be recorded elsewhere and a new shift will start. The value should return to 0 gallons at that point and begin to count again.

EDIT 2: I upgraded my "brute force" method to the trapezoid rule as opposed to the rectangle rule. Didn't change anything about the problem, but now my calculated sum and the PI calculated data sum exactly match when all of the data exists (using start and end times that are in the past).

Hi David,

There is an explanation for the seemingly random calculations when you use an end time beyond '*'. Whenever summary calculations such as "Total" are performed, the entire time range is always used, even if the end time extends into some time in the future. To get the final result, the calculation for the time period is divided by the fraction of time for which there are good values ('Percent Good'). The problem with using end times in the future is that the time range from '*' to the end time is considered non-good, because the snapshot value cannot be extrapolated beyond '*'. Thus, I expect that assuming the rest of your data up to '*' is good, multiplying the random-looking result by the 'Percent Good' (you can choose to have DataLink return this value), should probably give you the result that you're expecting.

I completely agree that this is confusing behavior, and the only documentation that I'm aware of is in the PISDK programmer's reference:

A possible workaround, as Leo noted, could be to add additional logic to smartly set the end time of the calculation. Otherwise, you could also just multiple the end result by the 'Percent Good' value (assuming the rest of the data is good). Another alternative would be to configure dedicated PI Points (could be an Analysis point, Totalizer point, PE point, etc) for storing the calculated values.