Group by Month, sorting by Year with that month

user031170 · 2021-11-15T17:13:33+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Accepted answers

user031170

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.

All comments

GrantSmith

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

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.

user031170

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

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

GrantSmith

@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

user031170

@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

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.

GrantSmith

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.