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

alek1111
alek1111 Member
in Charting

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

image.png

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.

image.png

Anyone has an idea on how to fix it?

Thanks

Alek

Best Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    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.

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

  • ArborRose
    ArborRose Coach
    Answer ✓

    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.

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

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    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.

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

  • alek1111
    alek1111 Member

    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
    ArborRose Coach
    Answer ✓

    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.

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

  • alek1111
    alek1111 Member

    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
    alek1111 Member

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

Categories