Can't seem to sort the dates on the X-axis

alek1111

Hello, I have 2 cards next to each other. One has a date field in the dataset already and another one date is calculated like this DATE(STR_TO_DATE(LEFT(Date + hour, 8),'%Y%m%d')) where the field "Date + Hour" field looks like this

Even though the calculated field shows as type "Date" the chart sorts it as if it was a string, see the chart on the right.

Anyone has an idea on how to fix it?

Thanks

Alek

ArborRose

It appears to be sorting…just not the sort you are looking for. When I do values using year & month, I create a monthname and a month (integer value). For example monthname(CURRDATE()) might give me "April". Whereas month(CURRDATE()) would give me "4". I put the monthname version on the card and put the month version on the sort. That way monthnames are sorted as January, February, March….etc. Instead of alphabetically. Looking at your image, that appears to be what is happening. The year is sorted but the months are being sorted by monthname rather than month number.

ArborRose

Try this formula: DATE_FORMAT(STR_TO_DATE(LEFT(Date + hour, 8),'%Y%m%d'), '%Y-%m-%d')

STR_TO_DATE(LEFT(Date + hour, 8),'%Y%m%d') extracts the date portion in the format YYYYMMDD.
DATE_FORMAT() function converts the date into the YYYY-MM-DD format, which should be sortable.

ArborRose

It appears to be sorting…just not the sort you are looking for. When I do values using year & month, I create a monthname and a month (integer value). For example monthname(CURRDATE()) might give me "April". Whereas month(CURRDATE()) would give me "4". I put the monthname version on the card and put the month version on the sort. That way monthnames are sorted as January, February, March….etc. Instead of alphabetically. Looking at your image, that appears to be what is happening. The year is sorted but the months are being sorted by monthname rather than month number.

alek1111

Thanks @ArborRose for your help. I have tried it, but no matter what I put in the Sorting field it seems to ignore it and still sorts by a character version of what it finds in the X-axis. I have tried 202404 or the full date 20240424 in the Sorting but it still won't use it.

As far as the value in the X-axis it is supposed to be a Date, with no special formatting, because it is tied to the Global filter on the dashboard.

Alek

ArborRose

Try this formula: DATE_FORMAT(STR_TO_DATE(LEFT(Date + hour, 8),'%Y%m%d'), '%Y-%m-%d')

STR_TO_DATE(LEFT(Date + hour, 8),'%Y%m%d') extracts the date portion in the format YYYYMMDD.
DATE_FORMAT() function converts the date into the YYYY-MM-DD format, which should be sortable.

alek1111

Thanks again for your efforts. Now it shows my calculated field as character "abc" and didn't change the sorting…

I'll try to add my date calculation to the ETL instead of the beast mode. I was trying to avoid it because it's someone else's dataset and transformation, but oh well at least it will hopefully do it.

I'll let you know the outcome.

Alek

alek1111

As I thought only after I added it to the dataset in ETL it started sorting normally. Thanks again for your help @ArborRose !