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
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
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).