Sorting an axis based on calculated field

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.

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