topic Calculate a range over an hour every hour in App Development

Calculate a range over an hour every hour

Eaun — Mon, 20 Dec 2021 21:38:40 GMT

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

Re: Calculate a range over an hour every hour

Mauritz_SA — Thu, 06 Feb 2020 10:02:17 GMT

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

Re: Calculate a range over an hour every hour

Eaun — Thu, 06 Feb 2020 10:13:37 GMT

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