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

Comments

  • rahul93
    rahul93 Contributor

    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.

  • 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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.