weekly sales dataflow
Can i create a dataflow that creates a column in the dataset that sums my daily level sales to weekly level sales? My input dataset has sales at the daily level, and i would like to join it to another dataset that only has sales at the weekly level, so I'd like to create a column that has the weekly level sales with the week number.
Best Answer
-
This sounds like a case where you'd want to use a GROUP BY statement in the dataflow. In MySQL, it would look something like this:
SELECT
YEAR(`Date`) AS `Year`
, WEEKOFYEAR(`Date`) AS `Week Number`
, SUM(`Sales`) AS `Total Sales`
FROM
TABLE_NAME
GROUP BY
`Year`
, `Week Number`
I included the Year as a field because if you had mulitiple years worth of data in your table, it would be aggregated into one set of weeks otherwise.
The same thing is possible in Magic ETL as well. You'd use the Date Operations transform to create a new column for the year and the week of the year. Then you'll add a Group By transform and add the new year and week of year columns you created as the columns to group by.
Either of these methods will get you a table of sales totals, aggregated by week number and year, which you can then join onto your other dataset.
I hope this helps! Let me know if you need more details.
2
Answers
-
This sounds like a case where you'd want to use a GROUP BY statement in the dataflow. In MySQL, it would look something like this:
SELECT
YEAR(`Date`) AS `Year`
, WEEKOFYEAR(`Date`) AS `Week Number`
, SUM(`Sales`) AS `Total Sales`
FROM
TABLE_NAME
GROUP BY
`Year`
, `Week Number`
I included the Year as a field because if you had mulitiple years worth of data in your table, it would be aggregated into one set of weeks otherwise.
The same thing is possible in Magic ETL as well. You'd use the Date Operations transform to create a new column for the year and the week of the year. Then you'll add a Group By transform and add the new year and week of year columns you created as the columns to group by.
Either of these methods will get you a table of sales totals, aggregated by week number and year, which you can then join onto your other dataset.
I hope this helps! Let me know if you need more details.
2 -
@pravaliya, did zcameron's reply help you out?
0 -
yes it did! thank you!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 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