MySQL Data Flow - Running Total - Specific Period?
Hi there!
I am currently working on a running total, which worked out fine. The only thing I can´t figure out is to calculate a running total per specific period.
My table should look like that:
As you can see our season starts on the first of May and ends on the last of April in the following year.
My current code for YTD
1) (table: ytd_figures)
SELECT a1.created_at_trans, a1.platform,(SELECT SUM(a2.total)
FROM proc_group a2
WHERE a2.platform = a1.platform AND a2.created_at_trans <= a1.created_at_trans)
AS ytd_total
FROM proc_group a1
ORDER BY a1.created_at_trans, a1.platform;
2) (table: proc_group_ytd)
SELECT n.*, n1.YTD_total
From proc_group n LEFT JOIN ytd_figures n1 ON n.created_at_trans = n1.created_at_trans
and n1.platform=n.platform
Could someone please show me where I need to include a statement for determining the time period?
Addional question: Is there any chance to include a window function, because they seem to be more efficient.
Thanks
Best Answer
-
@nlonkai you can also do window functions in Magic ETL as well. There is an article in the knowledge base that goes over the steps: https://knowledge.domo.com/?cid=etlactionsrankandwindow
Former Domo employee you can find me in the Dojo Community here @n8isjack2
Answers
-
Hi @nlonkai ,
Happy to help figure this out. This would be relatively easy to solve using a window function. Do you have access to Redshift dataflows in your Domo instance? If not, I would recommend asking your Domo Account Executive for access. Redshift dataflows allow you to use window functions.
Here is how I would recommend doing this in a Redshift dataflow:
- Create a new column to order the dates by. This is easy to do if you have a fiscal calendar already loaded into Domo. Day 1 of the year would be 5/1, while day 365 would be 4/30. Let's call this "Date_Order".
- Shift the date forward by 1 year for all dates that fall between 5/1 and 12/31. E.g., 5/1/2016 becomes 5/1/2017.
- Now, you are ready to use a SUM window function:
select "date", "total", sum("total") over (partition by date_part(year, "date") order by "Date_Order"
unbounded preceding) as sum from your_table order by "date"
This example of a window function allows you to accomplish the piece you are missing, which is partitioning the data by your fiscal year. This is the part of the example code that says, "partition by date_part(year, "date")"
Let me know if this makes sense, and if you have any other questions about this. This could be done in MYSQL as well, but it would be a lot more complex, and may not be as scalable. For these reasons, I recommend going with a Redshift dataflow if you can.
1 -
@nlonkai you can also do window functions in Magic ETL as well. There is an article in the knowledge base that goes over the steps: https://knowledge.domo.com/?cid=etlactionsrankandwindow
Former Domo employee you can find me in the Dojo Community here @n8isjack2 -
sorry for my late answer. We had issues with our Domain.
It worked out perfectly (and simple) with ETL Ranking and Window.
Thanks
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 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