Sorting an axis based on calculated field

Question & Answer
1

I have a bar chart that I want to be sorted based on a calculated field.

My bar chart
My bar chart counts the number of records per category, where the types of categories are determined by a control. For example, if the control is set to “weekday”, counts per day of the week are shown. But if the control is set to “weather type”, counts are shown of records where the weather type was sunny, cloudy, rainfall, etc…

What I want
If a temporal categorization is chosen, I want the axis to be sorted accordingly, hence Monday - Tuesday - Wednesday - … - Sunday, for example. However, since there is no logical order in weather types, I would like to sort such an axis by the number of occurrences.

Problem
I can solve the first problem by making a calculated field for these temporal categories, assigning a 1 to Monday, a 2 to Tuesday, etc. But I cannot set a different sorting strategy for different displays of the same visual, so I figured I should also make a calculated field to determine the order for the weather types. I tried to do this by using the countOver() function, partitioned by weather type and with PRE_AGG selected, with the intention of making it independent of data aggregations or filters. When I try to sort on this calculated field, I get the error that “a field used in the visual is no longer available in the current dataset”.

Does anyone have an idea on how to solve this?

1 Like
2

Hi @harmen -

This is a great suggestion, this type of advanced sorting is not possible today.

Here is a workaround you can use.

Step 1 - Create a calculated field to switch the dimension of your chart based on a parameter value
c_measure_dim

// switch dim order by on parameter selection
ifelse(
${pmeasure}='Day of Week',dow,
${pmeasure}='Weather Type',{weather_type},
null
)

Step 2 - Create a calculated field to convert your date to a day of week (integer).
c_week_day
extract('WD',dt)

Step 3 - Use the rank function to rank the weather type by number of occurrences
c_weather_type_rnk

rank([count(dt) ASC])

Step 4 - Create calculated field for a custom sort order that will switch the sort measure based on the parameter

// switch custom sort order by on parameter selection

ifelse(

${pmeasure}='Day of Week',max({c_week_day}),

${pmeasure}='Weather Type',{c_weather_type_rnk},

null

)

Result:
2022-07-15_12-44-32 (1)

3

works great! Thanks a lot