Averages over 5, 10, 30 days

Reply
Highlighted
White Belt

Averages over 5, 10, 30 days

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

Highlighted
Green Belt

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.

Highlighted
Red Belt

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`), 😎 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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
How to create lag functions in Card #Analyzer using #BeastModes ##### CONSULTING SERVICES #### I have left Domo to start my own consulting company, #OnyxRepo...
Highlighted
Black Belt

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.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.