Running total: ETL or MySql?

DHB_1
DHB_1 Member
in Magic ETL

I want to create a running total that resets every year. I've seen posts for doing it with ETL and a window function and others for MySQL and I am lost on which way would be best.

The data table is similar to this :
Month            Revenue Reimbursables
1/1/2018          100              5
2/1/2018            97           11
3/1/2018         102              8  etc.

 

In ETL, I calculate the Net Revenue (for the running total) and extract the year from the date. When it comes to the window function, what is the preceding and following? Where do I enter to reset when year changes? Thanks

Best Answer

  • Godiepi
    Godiepi Coach
    Answer ✓

    Hi , based on the 3 fields you provided , the following code will get you the same 3 fields plus 2 more fields for Revenue running total and Reimbursables running total.  both reset every year and start the sum in running total.   here it is the MySql code that I normally use

     

    Select 
    `Month`
    ,`Revenue`
    ,`Reimbursables`
    ,(Select sum(`Revenue`)
      		from `yourtable` as b 
      			where year(a.`Month`) = year(b.`Month`)
      			and a.`Month` >= b.`Month` ) as 'Running_Total_Revenue'
    ,(Select sum(`Reimbursables`)
      		from `yourtable` as b 
      			where year(a.`Month`) = year(b.`Month`)
      			and a.`Month` >= b.`Month` ) as  'Running_Total_Reimbursables'         

    From `yourtable` as a
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • Godiepi
    Godiepi Coach
    Answer ✓

    Hi , based on the 3 fields you provided , the following code will get you the same 3 fields plus 2 more fields for Revenue running total and Reimbursables running total.  both reset every year and start the sum in running total.   here it is the MySql code that I normally use

     

    Select 
    `Month`
    ,`Revenue`
    ,`Reimbursables`
    ,(Select sum(`Revenue`)
      		from `yourtable` as b 
      			where year(a.`Month`) = year(b.`Month`)
      			and a.`Month` >= b.`Month` ) as 'Running_Total_Revenue'
    ,(Select sum(`Reimbursables`)
      		from `yourtable` as b 
      			where year(a.`Month`) = year(b.`Month`)
      			and a.`Month` >= b.`Month` ) as  'Running_Total_Reimbursables'         

    From `yourtable` as a
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • DHB_1
    DHB_1 Member

    That's wonderful! Thank you.

  • Godiepi
    Godiepi Coach
    Great, Let me know if you run into any issue. as far as what method is better between ETL and MySQL, it all comes down to which one you feel more comfortable with... Personally , I prefer MySQL while other team mates like ETL more.
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • DHB_1
    DHB_1 Member

    Thanks again.

Categories