Group by Month, sorting by Year with that month

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.


Best Answer

  • user031170
    user031170 ⚪️
    Accepted 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.


Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • 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 Portland, Oregon 🟤

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


  • GrantSmith
    GrantSmith Indiana 🔴
    edited November 15

    @user031170

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


    And then sorting by Month then Year?


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

  • GrantSmith
    GrantSmith Indiana 🔴

    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.