Rolling Averages

Charlie Bantoft

Hi all. There's something so simple in Google Sheets / Excel that I cannot believe the might of Domo can't do it but a) I cannot work it out and b) it seems from going through the forums, it cannot be done easily. There hasn't been a post on it for 6 months so I'm hoping there may have been some developments.

I won't to calculate a rolling average for x number of days - where x would be a variable that dashboard users can slide up and down.

Any thoughts welcome. Thank you. Screen shot shows what I mean using google sheets (so easy)

MarkSnodgrass

You can do this by adding a variable control and then building a beast mode that is going to be a bit lengthy.

In my example, I created a slider control called lag count that is a number that slides from 1-30.

Next, I created a beast mode that will have a lot of case statements to build out the necessary number of days that you want to have in your rolling average. Here it is built out to the first three.

(


CASE WHEN Lag Count > 1 THEN

LAG(SUM(d), 1) OVER (ORDER BY dt)

ELSE 0

END +

CASE WHEN Lag Count > 2 THEN

LAG(SUM(d), 2) OVER (ORDER BY dt)

ELSE 0

END +

CASE WHEN Lag Count > 3 THEN

LAG(SUM(d), 3) OVER (ORDER BY dt)

ELSE 0

END +

0

) / Lag Count

In the above, "Lag Count" is the name of my variable and "d" is the field I am averaging. You can then drag this field int your table car and the variable control will be available for use.

It would be nice if it wasn't necessary to build out all of the case statements, but there currently is not another way (to my knowledge) to do it in a beast mode.

MarkSnodgrass

Correct, you would create 5 rank & window tiles and then append them. I would also add a constant for each of them with the number of days that it is (7,30,90,180,365) so that you can use your variable slider on your card to then filter to the proper group of data.

Missing days are important to take into account in both scenarios. If you do have missing days, you could join your dataset to a complete list of dates (such as the Calendar dataset in Domo Dimensions) that would add a row for any missing dates that you have. This will allow you to use the lag appropriately.

MarkSnodgrass

You can do this by adding a variable control and then building a beast mode that is going to be a bit lengthy.

In my example, I created a slider control called lag count that is a number that slides from 1-30.

Next, I created a beast mode that will have a lot of case statements to build out the necessary number of days that you want to have in your rolling average. Here it is built out to the first three.

(


CASE WHEN Lag Count > 1 THEN

LAG(SUM(d), 1) OVER (ORDER BY dt)

ELSE 0

END +

CASE WHEN Lag Count > 2 THEN

LAG(SUM(d), 2) OVER (ORDER BY dt)

ELSE 0

END +

CASE WHEN Lag Count > 3 THEN

LAG(SUM(d), 3) OVER (ORDER BY dt)

ELSE 0

END +

0

) / Lag Count

In the above, "Lag Count" is the name of my variable and "d" is the field I am averaging. You can then drag this field int your table car and the variable control will be available for use.

It would be nice if it wasn't necessary to build out all of the case statements, but there currently is not another way (to my knowledge) to do it in a beast mode.

Charlie Bantoft

Thanks so much, Mark. I had wanted to let users slide from 7 to 365 but I'll have to do it in pretty large steps otherwise I'll be writing that out until Christmas!

I can't believe there isn't an ETL solution or beast mode that's easier. Crazy as it's something most people want to see.

Thanks again. Charlie

MarkSnodgrass

You could do this in ETL with the Rank and Window function, but you would need to create a lot of rank and window tiles and then append them all together.

Charlie Bantoft

Yeah - I've seen that option. How many would I need if I want to give dashboard users the option to do 7day, 30day, 90day, 180day and 360day rolling averages? 5?

Also: with your earlier solution and with the Rank & Window option, would it matter if days (typically Saturdays and Sundays) are missing?

Thank you, again.

MarkSnodgrass

Correct, you would create 5 rank & window tiles and then append them. I would also add a constant for each of them with the number of days that it is (7,30,90,180,365) so that you can use your variable slider on your card to then filter to the proper group of data.

Missing days are important to take into account in both scenarios. If you do have missing days, you could join your dataset to a complete list of dates (such as the Calendar dataset in Domo Dimensions) that would add a row for any missing dates that you have. This will allow you to use the lag appropriately.

marcel_luthi

Would be good to highlight that if you go the ETL route, things will go eerie if besides the selection of # of days, the users (or you) need to apply apply filters to the card level, as the Rank and Window Tiles won't be responsive to these filters and might end up throwing odd results (One of the key difference on when to use a ETL vs BeastMode).

Thinking a bit more on this, if creating a new Dataset is not a big problem for you, I think I got a solution:

Now let's deep dive on how this works:
You have a dataset that looks similar to your Google Sheet, a set of dates with a single value for each date:

You create a ETL that self joins the dataset into itself and then filters to keep only the previous dates next to the current one:

The to Join operations just add a 1 to each to do a product of multiplying the table by itself with the Join Data.

This returns way more rows than what we need, so now that we've renamed to old values as Preexisting, we filter those out prior to storing to the new Dataset. Since in your current example, you're not using the current date value as part of the Average calculation, we check that the Date needs to be higher than the preexisting one, and that the Preexisting one is greater than the Date moved 365 days back (since you mentioned this is the limit on how far back users will be able to update the range, if you want to allow to go even further back just change this to the right value).

With this you now have a Dataset that contains as many entries for each Day as there were entries that matched to be in the look back period. Something like:

From here, it is easy to build a card that will have a calculated field that leverages the Variable we created (`Days Threshold` in my case), to determine which values should be included as part of the average:

And then you just use it in your card as shown on the first screenshot.

This was a good thinking exercise prior to the weekend :)

PD. Keep in mind that the way the BeastMode was written is to simulate the function you had in Sheets, in which missing days won't affect the Average calculation and it'll be made only using the available data points, should you want to change this behavior, you would divide by the Variable instead. Also this will only show dates for which you have data, you'd need to adjust the ETL to fill in missing dates between the ToJoinA and Join Data Operations, this to prevent adding dates with no values as preexisting entries which would in turn affect the Beast Mode.

Charlie Bantoft

Hi Marcel. Thanks so much for thinking hard about this. It's been a public holiday today in the UK so I'm only just looking at it this evening and can see I need tomorrow morning's fresh eyes (and mind on it). I think I understand what you're doing though and that does seem the simplest solution. I'll wrap my head around it tomorrow.

Thank you again.