Group by Month, sorting by Year with that month

user031170
user031170 Member
in Charting

I was able to get the following created in Domo, but it is not sorting my data correctly, as I want a stacked bar and then grouped by Month and then Year. So, I want January data from 2019 first, January 2021 second then February 2019, followed by February 2021 and so on. I am trying to mirror a setup from a co-worker on his data from Tableau on the last screen shot. I have tried a number of changes to ETL, Beast modes, sort order, charts, etc and can't get it to look correct. Thanks for any suggestions.

image.png
image.png


Best Answer

  • user031170
    user031170 Member
    Answer ✓

    Thanks again for both of your help, as here is the end result. I learned how and what a trellis does, as I hadn't had a need before.

    image.png


Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user031170

    You can use a beast mode to calculate the Month for your date and another for the year of your date:

    MONTH(`dt`)



    YEAR(`dt`)


    Then sort based on those two beast modes. Are you also graphing by month in your date selector in the upper right?

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

    I would create this for a beast mode and then add this to your sorting:

    MONTH(`dt`) + (YEAR(dt)/10000)


    This will make your dates become numbers with decimals where the year is to the right of the decimal place and the month is to the left of the decimal place.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user031170
    user031170 Member

    I have tried both of them and have tried sorting by using them, but get either the same or different results that make it worse. Also tried doing this formula CONCAT(MONTHNAME(`Dt`),' - ',YEAR(`Dt`)).

  • MarkSnodgrass
    MarkSnodgrass Coach

    Odd. I tested it in my instance with some sample data and it came out looking good.

    image.png


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    edited November 2021

    @user031170

    Have you tried using the trellis option on your bar chart and graphing by year?

    Screen Shot 2021-11-15 at 12.36.10 PM.png


    Screen Shot 2021-11-15 at 12.36.00 PM.png

    And then sorting by Month then Year?

    Screen Shot 2021-11-15 at 12.49.02 PM.png


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

    @MarkSnodgrass Able to finally get it to sort correctly using MONTH(`Event_Dt`) + (YEAR(`Event_Dt`)/10000). Really not sure why the other ways I was doing wasn't working. @GrantSmith Not aware of that feature, will test now

  • user031170
    user031170 Member
    Answer ✓

    Thanks again for both of your help, as here is the end result. I learned how and what a trellis does, as I hadn't had a need before.

    image.png


  • GrantSmith
    GrantSmith Coach

    One other thing to note is you can use the name of the month instead of the number via a beast mode:

    MONTHNAME(`dt`)

    Then use that beast mode in your Category 2 field.

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

Categories