Running total: ETL or MySql?
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
-
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 aDomo 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'3
Answers
-
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 aDomo 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'3 -
That's wonderful! Thank you.
0 -
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'1 -
Thanks again.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive