Reverse Running Total by Date

user067410
user067410 Member
in Magic ETL

Can I get some help in understanding how to take the reverse of cumulative totals based on date? I've attached a sample of the data, which has US State and County cumulative data by date and I would like to calculate total from one date vs the previous date and keep the grouping of state and county. 

 

Any help would be much appreciated. 

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    ... if you google both "cumulative sum domo" and "running total domo" there are solutions that immediately pop up.  

     

    is there something specific that stops those solutions from being applicable?

     

    https://www.youtube.com/watch?v=ZPf41Fjn1H8 // how to calculate cumulative sum.

    https://www.youtube.com/watch?v=cnc6gMKZ9R8 // how to use lag to get yesterday's value.

    ---

     

    the above solutions work, but force you to keep each date on the axis (in order to use LAG).  if you don't want to keep date on the axis, then you can use an offset calendar 

    https://www.youtube.com/watch?v=CDKNOmKClms

    You can use the same Window function  and can avoid LAG()

     

    OR just use a case statement sum(case when date = today() - 1 day end. )  -- this is pseudocode.

     

    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"

Categories