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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    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.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    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.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Answer ✓

    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 heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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 heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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. 

  • 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?

This discussion has been closed.