Organize values on X-Axis

Ben_Gilbert

Hi All, I am trying to sort my x-axis values on roles (see screenshot attached) not on the default sort options available in Analyzer.

Screen Shot 2023-09-27 at 12.54.28 pm.png

I read a previous note from 2017 that told me to use CASE to sort, so I did:

CASE split_detail WHEN 'CEO'THEN 1 WHEN 'Head of Business' THEN 2 WHEN 'key management personnel(KMP)' THEN 3 WHEN 'Other Executives/general managers' THEN 4 WHEN 'Senior Managers' THEN 5 WHEN 'Other managers' THEN 6 END

However, when I put this equation into the sort field, it dodn't sort in the way I wanted. Any help is greatly appreciated.

Thanks

Ben

ColemenWilson

Rewrite it like this and ensure that there isn't aggregation on the sort, in the screenshot you have it set to COUNT. It should look like this:

CASE WHEN `split_detail` = 'CEO' THEN 1
WHEN `split_detail` = 'Head of Business' THEN 2
WHEN `split_detail` = 'key management personnel(KMP)' THEN 3
WHEN `split_detail` = 'Other Executives/general managers' THEN 4
WHEN `split_detail` = 'Senior Managers' THEN 5
WHEN `split_detail` = 'Other managers' THEN 6
ELSE 7 END

Ben_Gilbert

thanks for that. I did as you outlined and it almost gets there! See screenshots. The only values that weren't in order were the Senior Manager and Other Manager (which should be in each other's position).

Screen Shot 2023-09-27 at 2.07.49 pm.png

Screen Shot 2023-09-27 at 2.08.55 pm.png

Any ideas?

ColemenWilson

Yeah it is because the values are case sensitive. So in the beastmode it doesn't find a match for "Senior Managers" because the "M" is capitalized in the case statement but not in the data, so it places it in position 7 from the ELSE part of the statement. A great way to protect against this is to wrap the values in LOWER() and then type out the text values as all lowercase.

CASE WHEN LOWER(`split_detail`) = 'ceo' THEN 1
WHEN LOWER(`split_detail`) = 'head of business' THEN 2
WHEN LOWER(`split_detail`) = 'key management personnel (kmp)' THEN 3
WHEN LOWER(`split_detail`) = 'other executives/general managers' THEN 4
WHEN LOWER(`split_detail`) = 'senior managers' THEN 5
WHEN LOWER(`split_detail`) = 'other managers' THEN 6
ELSE 7 END

One other suggestion would be to change the chart type to be a 100% stacked bar since you are comparing two % values that will always sum to 100%. Line charts are typically used to show trends over time.

ColemenWilson

Rewrite it like this and ensure that there isn't aggregation on the sort, in the screenshot you have it set to COUNT. It should look like this:

CASE WHEN `split_detail` = 'CEO' THEN 1
WHEN `split_detail` = 'Head of Business' THEN 2
WHEN `split_detail` = 'key management personnel(KMP)' THEN 3
WHEN `split_detail` = 'Other Executives/general managers' THEN 4
WHEN `split_detail` = 'Senior Managers' THEN 5
WHEN `split_detail` = 'Other managers' THEN 6
ELSE 7 END

Ben_Gilbert

thanks for that. I did as you outlined and it almost gets there! See screenshots. The only values that weren't in order were the Senior Manager and Other Manager (which should be in each other's position).

Screen Shot 2023-09-27 at 2.07.49 pm.png

Screen Shot 2023-09-27 at 2.08.55 pm.png

Any ideas?

ColemenWilson

Yeah it is because the values are case sensitive. So in the beastmode it doesn't find a match for "Senior Managers" because the "M" is capitalized in the case statement but not in the data, so it places it in position 7 from the ELSE part of the statement. A great way to protect against this is to wrap the values in LOWER() and then type out the text values as all lowercase.

CASE WHEN LOWER(`split_detail`) = 'ceo' THEN 1
WHEN LOWER(`split_detail`) = 'head of business' THEN 2
WHEN LOWER(`split_detail`) = 'key management personnel (kmp)' THEN 3
WHEN LOWER(`split_detail`) = 'other executives/general managers' THEN 4
WHEN LOWER(`split_detail`) = 'senior managers' THEN 5
WHEN LOWER(`split_detail`) = 'other managers' THEN 6
ELSE 7 END

One other suggestion would be to change the chart type to be a 100% stacked bar since you are comparing two % values that will always sum to 100%. Line charts are typically used to show trends over time.

Ben_Gilbert

Hi Colemen

It isn't working for me, see screen shots attached.

The reason why I use a line chart is because it is an industry-standard way of presenting the difference in male/female management roles on a seniority

Screen Shot 2023-10-10 at 10.28.26 pm.png

Screen Shot 2023-10-10 at 10.29.01 pm.png

basis.

ANy help appreciated.

BEn