Aggregating 10 years of data to display by day-of-month AND month (combining all years for each day)

JPeG
JPeG Member
in Magic ETL

Well hello there fellow domo dojonians, i'm hoping someone can help. This may turn out to be super simple and i'm just over thinking it, so apologies in advance if that's the case. 

 

I have a data set that includes 10 years of data. The date column includes day, month, year and specific time of day. 

 

What i would like to do is to show all ten years aggregated into a single year view, by day of month. I seem to be able to make a chart that aggregates all months from the 10 years and can show that data. I can also do a chart that shows all days of all months combined. What i can't seem to do is show separate months, while aggregating 'day of month' . 

 

Hope that makes sense. basically, i want the graph to show the day of month, separated by month (so the X axis has Jan 1, 2, 3...31, Feb 1,2,3... etc. ) and add up all the entries from each individual 'day of month' across all 10 years. So Jan 1 - 34 entries, Jan 2 - 78, Jan 3... and so on. 

 

Any help appreciated, as always

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @JPeG 

     

    You could create a beast mode to extract only the date parts you're interested in.

     

     

    CONCAT(LEFT(MONTHNAME(`Date`), 3), '  ', DAY(`Date`))

     

    Left is just making a 3-letter month abbreviation of the name since MONTHNAME returns the entire name.

     

    Because this is now a string it will sort alphabetically rather than by calendar. To fix this issue you'd need to create a separate beast mode to sort properly based on a numerical representation.

     

     

    MONTH(`Date`) * 100 + DAY(`Date`)

     

     

    Drag that beast mode into your sort column and it should sort the days correctly.

     

    If you even wanted to you could also create a beast mode for the year to use as a series.

     

    YEAR(`Date`)

     

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @JPeG 

     

    You could create a beast mode to extract only the date parts you're interested in.

     

     

    CONCAT(LEFT(MONTHNAME(`Date`), 3), '  ', DAY(`Date`))

     

    Left is just making a 3-letter month abbreviation of the name since MONTHNAME returns the entire name.

     

    Because this is now a string it will sort alphabetically rather than by calendar. To fix this issue you'd need to create a separate beast mode to sort properly based on a numerical representation.

     

     

    MONTH(`Date`) * 100 + DAY(`Date`)

     

     

    Drag that beast mode into your sort column and it should sort the days correctly.

     

    If you even wanted to you could also create a beast mode for the year to use as a series.

     

    YEAR(`Date`)

     

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • JPeG
    JPeG Member

    That worked perfectly - thank you so much! Sometimes you stare and stare and stare too long! hah ? 

     

    Appreciate the help, thanks again! 

Categories