Group data by week

user095063

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.

MarkSnodgrass

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.

MarkSnodgrass

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.

jaeW_at_Onyx

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.

jaeW_at_Onyx

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.

user095063

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. 

MarkSnodgrass

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.

user095063

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?

MarkSnodgrass

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.

jaeW_at_Onyx

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.