Change chart sort order based on variable value.

MarioR

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?

GrantSmith

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.

GrantSmith

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.

MarioR

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

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.