7 Replies Latest reply on Dec 30, 2010 10:09 AM by hanyong

# Datalink Power (kW) summation

I need to create an Excel query with Datalink that will take a power tag and for the month take the 1st hour of every day (30 or 31) sum the 30 first hours up and display them in the first cell the the 2nd hour of everyday for the month in the second cell and so on.

Is there a fairly ingenious way of doing this versus having to put 1st hour of every day on the spreadsheet and sum with excel?

• ###### Re: Datalink Power (kW) summation

Hi Kenneth

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.

• ###### Re: Datalink Power (kW) summation

Han,

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

Thanks Again

• ###### Re: Datalink Power (kW) summation

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?

thanks

• ###### Re: Datalink Power (kW) summation

Glad that what I provided was of some help

Kenneth Rice

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?

• ###### Re: Datalink Power (kW) summation

Han,

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

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.

Thanks again

If it matters the Power Tag is collected in kW and the report numbers are Mega-Watt

• ###### Re: Datalink Power (kW) summation

Han,

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