Group data by week
Hi. I have a dataset with three columns. Date, Revenue, Advertiser. I need to group the revenue so that it shows weekly. I expect the data looks something like the following:
```
Date Advertiser Revenue
Week 1 abc 123
Week 2 abd 124
```
I tried SQL but seems like I have a syntax error. Any help would be greatly appreciated! Can use either SQL or Magic ETL.
Thank you.
Best Answers
-
To group by week in the ETL, you will want to use the date operations tile. Drag that tile in and select week of year and name it week number. I would also add another column to get the year and select year of date in the date function. In your group by tile, group by those two columns and sum your revenue. This will get you your totals by week.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Just add Advertiser in your group by tile in the same section where you added the week number and year. It will then sum the revenue by advertiser, week number, and year.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Be careful with this approach.
Keep in mind that year 2019 may end in the middle of Week 52. Make sure to test and verify how that plays out in your GROUP BY statement.
ex if Jan 1 2020 was a Wednesday then
12/31/2019 would be Week 53 2019
1/1/22020 would be Week 53 2020.
When I have to deal with weeks like this i usually create a fiscal calendar as a separate table and then join it in.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
Is there a reason why you can't generate this information in a Card?
do you have a screenshot of the work you've already done?
there is a GROUP BY tile in MagicETL and standard MySQL will work as well.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Yes, there is. The Advertiser column has a lot of values and I need to break the data by Advertiser. Eventually, I have to show the difference between the sum of Revenue of 2019 and 2020. And I want to see it weekly.
So the end goal is to see something like this:
Advertiser Date 2019 Revenue Date 2020 Revenue
I figured the first step would be to group all the data by weekly and then divide it onto years using Union All. In magic ETL I am not sure how to group the data by weekly. This is the screen shot.
0 -
To group by week in the ETL, you will want to use the date operations tile. Drag that tile in and select week of year and name it week number. I would also add another column to get the year and select year of date in the date function. In your group by tile, group by those two columns and sum your revenue. This will get you your totals by week.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thank you for your solution, It seems like I get the year right and the week, and it did sum the Revenue, but the only question I have now is how would I break it by Advertiser? This is what I have as an output. Which transformation should I use?
0 -
Just add Advertiser in your group by tile in the same section where you added the week number and year. It will then sum the revenue by advertiser, week number, and year.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Be careful with this approach.
Keep in mind that year 2019 may end in the middle of Week 52. Make sure to test and verify how that plays out in your GROUP BY statement.
ex if Jan 1 2020 was a Wednesday then
12/31/2019 would be Week 53 2019
1/1/22020 would be Week 53 2020.
When I have to deal with weeks like this i usually create a fiscal calendar as a separate table and then join it in.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
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