Could you post some sample data?
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.
Sample Apple data.xlsx 17.2 KB
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.
Thanks a ton, I'm going to have to digest this a bit but it looks good. Many thanks.
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?