In DataLink, I would like to enter a calculated data expression that does a day-by-day total of a tag.. but only for the first 8 hours of each day. So each cell should contain the total of the tag for the first 8 hours of each day.

I thought that this would work but it does not:

Can this be done in DataLink, and if so, how?

Hi Steve,

As you've already realised, this is a bit more complex than it first seems.

So as not to bore you/anyone else reading this for a quick answer, the best solution I can come up with is to not use the Calculated/Advanced calculated value function and instead use Summary values with an expression (such as TagTot('Sinusoid','*','*+8h') ). The reasoning and details follow.

From the surface, your filter approach would seem like the best answer. As you've noticed, the "PI World" can be a strange place....There are a few issues you'll run into using a filter expression like "hour('*') < 8".

To test our solutions, I calculated the total for the first 8 hours of Sinusoid for a number of days (and it always came out to the same number - which is expected). For my data, I got a value of 22.478 Using a calculated data from 't' to 't+8h'. This is the value we hope our solution would get to.

First off, you'll likely run into an error "[-11101] All data events are filtered in summary calculation". This is the first head-scratcher. The filter expression is only evaluated once (I can't confirm if it's per time interval or per execution, but the results are the same) and uses the time of execution as '*', not the start of the period or per each event. This means that your calculation will only run without errors if you refresh your spreadsheet before 8:00am. Furthermore, that calculation will return the total for the entire period, not just the first 8 hours. Luckily, this is an easy fix; we just need to change the Expression sampling method to "interpolated" and it will start providing values. Unfortunately, they're the wrong values (we expect 22.478):

So, what happened? We need to specify a sampling interval and this can cause our data to be misrepresented in the calculation. Increasing the sample rate provided a slightly more accurate number, but still nowhere near the desired value. Why is that? Well, I'll need to preface this by saying "I could be wrong", but my hunch is that his is a due to the way the calculated data interpolates a value at the boundary of your time period. The calculation is being performed over the full 24 hour period, not just the first 8 hours. For the remaining 16 hours, we've filtered out the events so there's nothing there, but the calculation will still be performed. Hopefully this crude diagram of the calculation performed on Sinusoid will help explain this better.

A total is essentially the area underneath our Blue** Line. As you can see, the value of 0 at the boundary skews our data.

What we'd really need is for the values from 8:00 until the end of the day be 0 so that we are not calculating an interpolated value for the rest of day. This isn't that hard to do. We can modify our calculated data to use an expression that does this for us and we won't need to use the filter expression:

So, now we are much closer to our expected value of 22.478. But we're not spot on. Why? I can only assume this is an issue with our boundary condition or a similar problem described above. Nevertheless, our value is wrong and that's less than ideal. You could work on your expression to try and get things 100% accurate, but I think it's a lot of effort and your time is most likely better spent elsewhere.

As stated at the start of this novella, my solution is to use sampled data and an expression:

As you can see, we got our expected value! Sampled data is going to execute the expression every [time interval] from [start time] to [finish time]. Using TagTot (or other similar functions - see the PE Reference on LiveLibrary) works fine as '*' is evaluated correctly and we can get perform our summary over the period we need, not the entire day (and not have to deal with our interpolation issues). Will this solution replicate replicated the functionality in the Calculated Data function? Not completely. You can't filter events from the calculation and you're limited to time-weighted calculations. Hopefully this is enough to see you through. If not, you'll probably need to get creative with PI Asset Analytics.

Wow. I didn't expect to write so much on this topic, I hope it's been helpful. Let me know if this unclear, you have further questions/comments/etc. I'd also gladly be proven wrong, so if anyone else has a better answer, I'm all ears!

Thanks!

--Sam

** I'm colorblind, it could also be purple. Many apologies