Change chart sort order based on variable value.

MarioR
MarioR Member
edited March 2023 in Variables

Hi,

I have created a variable that I am using on the y-axis of a bar chart. With the toggle letting switch between categorical and date values. I would like to be able to alternate how the y-axis is sorted based on which variable value is currently on the chart:

Date values: The date values are calculated by year and I would like the data sorted from the most recent to oldest year.

Categorical: Have the data sorted by total value in descending order.

When trying to sort the values manually in descending order everything looks fine except for date values which are then out of order based on the date values.

Is there a beast mode or setting that I can create that allows the sort order to dynamically change based on the data type of the variable?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can utilize your variable and select the different fields depending on the value of your variable:

    CASE WHEN `variable` = 'Date Value' THEN `date field` ELSE `category field` END

    Toss that beast mode into your sort and select sort descending.

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can utilize your variable and select the different fields depending on the value of your variable:

    CASE WHEN `variable` = 'Date Value' THEN `date field` ELSE `category field` END

    Toss that beast mode into your sort and select sort descending.

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

    I was able to create the custom sort using the following formula to accomodate the date field.

    CASE
    WHEN Variable = 'Date'
    THEN MAX(CASE WHEN YEAR(Date) = YEAR(CURDATE()) THEN 10
    WHEN YEAR(CURDATE()) - YEAR(Date) = 1 THEN 9
    WHEN YEAR(CURDATE()) - YEAR(Date) = 2 THEN 8
    WHEN YEAR(CURDATE()) - YEAR(Date) = 3 THEN 7
    WHEN YEAR(CURDATE()) - YEAR(Date) = 4 THEN 6
    WHEN YEAR(CURDATE()) - YEAR(Date) = 5 THEN 5
    WHEN YEAR(CURDATE()) - YEAR(Date) = 6 THEN 4
    WHEN YEAR(CURDATE()) - YEAR(Date) = 7 THEN 3
    WHEN YEAR(CURDATE()) - YEAR(Date) = 8 THEN 2
    WHEN YEAR(CURDATE()) - YEAR(Date) = 9 THEN 1
    WHEN YEAR(CURDATE()) - YEAR(Date) >= 10 THEN 0
    END)
    ELSE COUNT(Other Value)
    END

    Using this variable I can leave the sort in descending order but when the variable is switched to date fields they are displayed in ascending order instead.

  • Ant T
    Ant T Member

    rather than all those individual WHEN statements, I just multiplied by -1 to invert the order. However my ELSE COUNT doesn't work. My value split out a ton into non unique values. Not sure why.

Categories