5 Replies Latest reply on Jul 22, 2019 5:50 AM by vairavan ganesh

# Rolling hour calculation for comparison

Let's say I have two weeks of data, down to the hour. I get my data updated hourly. I would like to compare:

Total today, up to the latest hour

vs.

Daily average of all previous days, up through the same hour.

So, let's say I get number of apples sold, hourly:

If it's 10am today, I want to compare all apples sold today through 10am, for all previous days in my data set, through 10am.

I'm guessing probably some calculated fields with date logic, but curious if these a quicker window calculation?

Jim

• ###### 1. Re: Rolling hour calculation for comparison

Hi Jim,

Could you post some sample data?

• ###### 2. Re: Rolling hour calculation for comparison

Sure thing, sample data attached, and here too is a "visual" of the comparisons that I need.

So for each hour of my latest date (in this case 8/31), as my data updates, it looks at the average of that time frame window for all days before it, i.e., red, blue, green indicates the analysis period for that nth hour.

My overall goal is basically to have a rolling comparison to see if today looks funny relative to the average of the period days.

• ###### 3. Re: Rolling hour calculation for comparison

See the attached. The key is to create a calculated field for the hour of the date in order to make addressing and partitioning easier, and then to partition the Daily Running Total calc on days (addressing on hour), and then create a WINDOW_AVG([Daily Running Total],FIRST(),0) that partitions on hours (addressing on days), with the nested Daily Running Total still addressing on hours.

My eyes glaze over when I see a table full of numbers, so I created another calculation the is the % difference from the hourly average, and used that that to create a highlight table/heat map. This calc does yet another level of nesting, with partitioning on Days, addressing on hours. With that, we can see that the first days in the data set had the best numbers, and that because the table is using averages of averages of running totals, by the time the end of the day rolls around the variation is smoothed out and the difference gets pretty small.

• ###### 4. Re: Rolling hour calculation for comparison

Thanks a ton, I'm going to have to digest this a bit but it looks good. Many thanks.

• ###### 5. Re: Rolling hour calculation for comparison

Hey Jonathan,

The file I'm trying to download says the source is missing when I try to open.

I'm onto something similar, can you share it again?

Thanks,
V