Averages over 5, 10, 30 days

tstimple · October 2020

Hey Team,

I am new to DOMO so I hope this may be something easier than i have figured out.

I want to create a line(?) chart that counts the items in one column (with an associated date field) and then provides me a moving average of those counts over the past 5 days, 10 days, and 30 days.

for example:

over the past 30 days there have been 35 incidents, of those 11 have occurred in the past 10 days, but only 3 in the past 5 days

3 numbers on a line 1.16, 1.1, 0.6

The numbers would change (slightly) every day.

Is it just a "Trend" ??

Help please...

Thanks

Tim

rahul93 · October 2020

you could try to create 2 beast modes, 1 for calc and other for label.

Calc Beast mode :

count(case when `date` <= current_Date - 5 then `incident` end) / case when `date` <= current_Date - 5 then 5 end

Label beast mode:

case when `date` <= current_Date - 5 then 'Last 5 days' end

Note: I have only given for 5 days, you can replicate for 10, 15,30 as per your requirement.

Let me know if this works.

GrantSmith · October 2020

Hi @tstimple ,

You can utilize the windowing function LAG to calculate the rolling average based on the number of days. Window functions are a feature switch so if you currently don't have access to them talk with your CSM about getting it turned on.

Example 5 day rolling window:

(LAG(COUNT(`one column`), 1) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 2) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 3) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 4) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 5) OVER (ORDER BY `Date`))
/ 5

10 day rolling window:

(LAG(COUNT(`one column`), 1) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 2) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 3) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 4) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 5) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 6) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 7) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), <span class="lia-unicode-emoji" title=":smiling_face_with_sunglasses:">?</span> OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 9) OVER (ORDER BY `Date`) +
LAG(COUNT(`one column`), 10) OVER (ORDER BY `Date`))
/ 10

And you can extrapolate for a 30 day rolling window.

@jaeW_at_Onyx has some really great tutorial videos that he's put out and has one specifically around this type of solution: https://www.youtube.com/watch?v=cnc6gMKZ9R8. I highly recommend giving that video a watch (and others on his channel). He gives great background and walks through the problem really well.

jaeW_at_Onyx · October 2020

ooh @GrantSmith thanks for the recognition!

just be advised the LAG function will always show the last N rows. it doesn't know if the previous row was yesterday or three weeks ago, LAG just pulls the previous row in the window. So be careful if you know you have gaps in your data.

Averages over 5, 10, 30 days

Comments

Categories