Running Total with Magic ETL (not MySQL)

swagner
swagner Contributor
in Magic ETL

I've made this short video to explain what I'm trying to do:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @swagner 

     

    You can use a Rank & Window Tile. Using SUM on your number field with an unbounded for the first setting and 0 for the last setting. Order by your date field and now partitions will get you a rolling sum / running total.

     

    Screen Shot 2020-12-09 at 11.09.04 AM.png

     

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @swagner 

     

    You can use a Rank & Window Tile. Using SUM on your number field with an unbounded for the first setting and 0 for the last setting. Order by your date field and now partitions will get you a rolling sum / running total.

     

    Screen Shot 2020-12-09 at 11.09.04 AM.png

     

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

    Thanks Grant!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @swagner  in SQL parlance that's called a cumulative sum.

     

    Which I mention because eventually you'll want to use a PARTITON BY which will reset your counter based on an Item, Customer or Year.

    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