I'm not sure what's the nature of your power tag, so when you say "take the 1st hour of every day", do you mean taking the total of the power tag for the 1st hour? Or the power tag should have 1 single value every hour and you just need to take that value?
Depending on what you want to get for the hour of every day, we can write a PE equation for that. For example, if I wanted to find the time-weighted total of a tag within an hour from the sampling/calculating time, the PE equation would be:
TagTot('tagname', '*', '*+1h')
Then using DataLink's Sampled Data, we can calculate a PI Expression (the above PE equation) for the 1st hour every day within a month by sampling daily (start time= 1st day of the month at 00:00:00, end time = last day of the month at 00:00:00 and Interval = 1d). Then you can use Excel's Sum function to sum the output from Sampled Data up.
Or you can also use Calculated Data, calculating a PI Expression (the same PE equation as above). Set the start time = 1st day of the month at 00:00:00, end time = last day of the month at 00:00:00. Calculation mode = total and Calculation basis = event-weighted (to find the sum). In addition, choose Expression Sampling = interpolated and Expression Sampling Frequency = 1d)
By defining a PE that calculates based on archive value within an hour and sampling daily, we are effectively extracting data from a specific hour within a day for calculation. The difference in the 2 options above is that the 1st option is finding the 30 or 31 day sum in Excel, whereas the 2nd option is using DataLink to do that.
You can increase the start time and end time by 1 hour to find the result for the next hour and so on.
Thanks for the insight. "do you mean taking the total of the power tag for the 1st hour?"
Yes the total of the 1st hour of every day then sum those total over the month. So the report would have hour 0,1,2...23 with complete total for the month.
Hopefully thats more clear !
We have been trying the example you gave and think we just need to tweak the conversion parameter and we will have what we want. the data is collected as kW and reported as MegaWatts.
P.S. I had told someone that Han would have the answer for us. Datalink seems to be your niche
One more question if I set up the formula above with 1h interval and a .024 conversion kw to mega-watt and then sum the results I get number close to our old report system thats good. Now if I use same formula with 1mo interval and sampling frequency with a 0.34 conversion. I get a close number to 1st formula sum, if I tweak 0.34 down to .337??? I get even closer.
So heres the question what is the math logic that causes PI to use 0.33??? to get the right result. My logic tells me that .72 the right conversion number 24 hrs a day times 30 days divided by 1000 kW in a Mega Watt?
Can anyone prove .33??? beyond a reasonable doubt? will this number always work even if its a 31 day month?
Glad that what I provided was of some help
Now if I use same formula with 1mo interval and sampling frequency with a 0.34 conversion. I get a close number to 1st formula sum, if I tweak 0.34 down to .337??? I get even closer.
For now a conversion of 0.33 seems weird to me as well.
At the same time, what I understand from your description is that you have another result by changed the sampling interval to run the PE equation from '1d' to '1mo' and changing the conversion factor. And this new result is compared with the result from calculating the sum of daily power consumption for a specific hour (the 1st formula sum)
Assuming you are still using the PE equation that calculates the time-weighted total of the power tag in an hour, I am not sure how the changes would give the same result. Perhaps there is something that I am missing out here?
Thanks for the help. Let me see if I can make this easier by giving you the formulas.
So if we take
and then filter for all the 00:00 AM we get 30 results for Nov which then we sum with Excel and we get X
then if I take
=PIAdvCalcExpDat("TagTot('PP01-75012-POWER', '*', '*+1h')",Sheet1!$B$2,Sheet1!$B$3,"30d","total","event-weighted","interpolated","30d",0,0.34,193,"?PIServer")
I get X within 1% or less
I would really like to use the second formula since I would like results in one cell versus 30 then sum, my problem is I can not come up with the logic of why .34 or a slightly smaller number then that returns correct results. Is it a one time fluke or can it be explained.
My management would also like to use the second formula but we need to be able to support the math behind it if ever questioned.
If it matters the Power Tag is collected in kW and the report numbers are Mega-Watt
GOOD NEWS !!! We got it, thanks to your help. My supervisor started hacking away at the formula a bit and here's what we got
=PIAdvCalcExpDat("TagTot('PP01-75012-POWER', '*', '*+1h')",Sheet1!$B$2,Sheet1!$B$3,"1mo","total","event-weighted","interpolated","1d",0,0.024,193,"?PIServer")
This gives us what we want and the 0.024 is understandable and can be mathematically Justified kW to Mega-Watt over the time factors
Thanks so much. Your truly a master of datalink ...
Cool, great that you were able to resolve this quickly and share your result