Rolling 3 Mo Avg - Breaks Card

Crisocir
Crisocir Member
edited December 2021 in Charting

I want to calculate rolling average of the previous 3 months.

I was going to do this using windowed lag functions

(

(LAG(SUM(`Sales Amount`),1) OVER (ORDER BY `YEARMONTH`)) +

(LAG(SUM(`Sales Amount`),2) OVER (ORDER BY `YEARMONTH`)) +

(LAG(SUM(`Sales Amount`),3) OVER (ORDER BY `YEARMONTH`))

)/ 3

Each Lag works individually but any time I try to do any computation between them the card breaks and throws the error:

An issue has occurred during processing. We are unable to complete the request at this time. 

Any idea of how I can get around this? Or is this no longer possible? I feel like I have done this in the past.


ad538fc03b40c753488075ddd525e49e.gif


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited December 2021

    "Mr. Window Function" @GrantSmith has a nice outline of rolling averages here:

    https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

    and yours looks pretty much like his 3 day rolling average example.

    Are there gaps in the data that are causing the issue? Did the formula validate when you are in the editor?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Crisocir
    Crisocir Member

    @MarkSnodgrass

    Yes the formula validates in the editor and I don't believe there are any gaps as It calculates each individual Lag perfectly fine throughout my entire dataset. This is really puzzling to me as I'm certain I have applied this logic before without issues.

    I'll take a look at that link and see if I can spot any differences

  • GrantSmith
    GrantSmith Coach

    Ha @MarkSnodgrass - Is there a badge for that?


    @Crisocir - Your beast mode appears to be correct. I have seen issues when there's too much data for the beast mode to handle in the card. What happens if you restrict your data to just this year?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Crisocir
    Crisocir Member

    @GrantSmith Good idea,

    Tried it, still broke. I'm going to try it with a very simple straightforward dataset and see if I can get it to work if so then I know the data may be the issue.


    Thanks

  • Crisocir
    Crisocir Member

    @GrantSmith @MarkSnodgrass


    I've gotten it to work with a very simple dataset so I guess the dataset is the issue back to the drawing board, but thats for your input!

Categories