Cumulated sum in a quarter using Magic ETL
Hello,
can you help me to calculate a cumulated sum in a quarter using Magic ETL ?
In this exemple:
Date | Product | State | Profit |
15-jan-2018 | Bicycle | Oregon | $ 100,00 |
16-feb-2018 | Bicycle | Ohio | $ 100,00 |
30-mars-2018 | Car | Oregon | $ 2 500,00 |
10-april-2018 | Bicycle | Oregon | $ 100,00 |
01-mai-2018 | Car | Ohio | $ 2 500,00 |
17-mai-2018 | Car | Montana | $ 2 500,00 |
the cumulated profit of 1Q would be $ 2 700 and for 2Q is $ 5 100.
I would like to use Magic ETL because we do not use MySQL in our project and the Beast Mode can't do it itself as I'am calculting with multiple rows in my data set.
Thank you very much in advance for your help.
Katerina
Best Answer
-
Ok, so I think I understanding what you're wanting. I took the original sample you gave and built the following just using a 'Running Total Line' card type (no ETL needed)
The only optional changes I made were to set the date range fitler to Graph by None
I set the 'Number of Running Total Series' to 1
And then I added the Product and State columns as quick filters
Now the quick filters will allow you select or remove certain variables and have your running total line update accordingly.
Let me know if you have any other questions,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
Answers
-
In the ETL process, you can use 2 'Date Operations' widgets, one to return the Quarter of Date and the other Year of Date. I would then use the Combine Columns option to combine those values into one new column.
From there you can do a 'Group By' on the new date from your Combine and sum the Profit amount.
That should leave you with a Quarter Date column and a Quarter Profit column. If you need additional levels of detail you can add columns to your Group By.
Hopefully this helps get you started.
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
Hello,
thank you very much but I had to expresse my need wrongly. Actully I need to follow my cumulated profit within a quarter (because I have an objectif by quarter) but I need to observe its evolution by day or by a week. In my exemple, the result I'm looking for would look like :
Also I need to be able to filter my indicater by other colomnes (in my exemple by State and Product). Your solution would give me one value for whole the quarter.
Thank you once more for your help.
Katerina
1 -
Ok, so I think I understanding what you're wanting. I took the original sample you gave and built the following just using a 'Running Total Line' card type (no ETL needed)
The only optional changes I made were to set the date range fitler to Graph by None
I set the 'Number of Running Total Series' to 1
And then I added the Product and State columns as quick filters
Now the quick filters will allow you select or remove certain variables and have your running total line update accordingly.
Let me know if you have any other questions,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2 -
YES, that is exactly what I need ! Thank you very very much !
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive