Calculate a range over an hour every hour

[Eaun]

Hi

Would like to do a rolling sum over an hour every hour not sure how to do it can someone point me in the right direction please.

Have included an excel sheet to show what I mean.

Any help will be greatly appreciated 

[Mauritz_SA · Accepted solution]

Hi @Eaun 

I am not sure why your second hour has all the hours? The following will work in script (I called your time column 'timecolumn'):

Source:
LOAD
timecolumn,
good,
bad,
ugly
FROM [lib://Downloads/ahh more hours.xlsx]
(ooxml, embedded labels, table is Sheet1);

Summary:
LOAD
Hour(timecolumn) AS Hour,
Sum(good) AS TotalGood,
Sum(bad) AS TotalBad,
Sum(ugly) AS TotalUgly
RESIDENT Source
GROUP BY Hour(timecolumn);

If you just want it in the front end then make your dimension Hour(timecolumn) and then just have Sum(good), Sum(bad) and Sum(ugly) as measures.

Hope this helps.

Regards,

Mauritz

[Eaun]

Thank you Maurtiz that's what I was thinking but was not sure about it .