Ordering Your Stacked Bar Based on Measure

Based on my own experience, ordering a stacked bar chart is not that simple. Sometimes clients want to sort the number from largest to the smallest. Meaning, to have the largest amount stacked at the bottom and the smallest at the top. Sometimes they want to have their brand at the bottom while competitors are stacked at the top. If you are facing similar requests and looking for a solution, please join me today.

Original Ordering Options

This graph shows the quantity by Industry from 2020 to 2022:

Original Ordering Options-Graph of quantity by industry from 2020 to 2022

Usually, we can order by year or Industry in descending or ascending order. In the above case, we have it sorted by year and Industry both in ascending order. In this way, the Banking Industry is always at the bottom and the Insurance Industry is always at the top.
What if I want to have the Industry with the largest quantity at the bottom and the smallest quantity at the top? How can we do that?

Order by Quantity (or any Measure)

If we need to order by quantity, first we need to know which Industry has the largest quantity and which one has the smallest. In SAQL, we need to introduce the ranking windowing function.

rank() over([..] partition by ‘Date’ order by sum(‘Quantity’) desc) as ‘Ranking’

Here I have the ranking and I can then use it to control the order. The trick here is, I can’t leave it as a measure, I need to have it as a dimension so I can use it in my grouping for ordering later. If you get confused, just continue following the example with me.

result = group result by (‘Date’, ‘Industry’, ‘Ranking’);
result = foreach result generate ‘Date’, ‘Industry’, sum(Quantity) as ‘Quantity’, ‘Ranking’;

Then you will notice that the ‘Ranking’ column shifts from right to left in the grouping columns:

Ranking column shifts in the grouping columns for quantity by industry table(left)
Ranking column shifts in the grouping columns for quantity by industry table(right)

Now we can put ‘Ranking’ in the order:

result = order result by (‘Date’ asc, ‘Ranking’ asc);

Now when you switch back to the Stacked Bar chart, it does not show you anything close to a stacked bar but this:

Stacked Bar chart does not show the right output

The resulting graph looks much better. It is ordering the quantity from largest to smallest – take 2021 as an example, you have 96k (largest) at the bottom and 30k (smallest) at the top. However, if you look at the legend, it does not just show the industry anymore but also the ranking. Since you now have ranking in your Bar Segments.

Don’t panic, here comes the magic. All you need to do is to go to your Formatting – Conditional Formatting – change Apply Conditional Formatting from None to Industry. Your color and the legend are all back to normal!

Hide industry in Stacked Bar Graph

It seems our work is done here. However, if you hover over the bar chart, the tooltip shows you the ranking as well. If you wish to have this dimension there, feel free to do so, especially if you have a large number of stacked items (instead of just 4 industries here), it can be helpful.

Conditional Formatting the Stacked Bar Graph

But if you wish to leave extra information out of the picture, go back to your Formatting – Tooltip – check the Customize data point details box – under Show measures, uncheck Ranking

Customize data point details box

Now the graph is more comprehensive, with all your information presented nicely with exactly what you are asking for – order based on the quantity!

All the information is presented as the order is based on the quantity

Order by Quantity – with customized Industry grouping

I like to think a bit further about the use case based on the above example. In some cases, when we have a large amount of Industry (dimension), instead of showing all of them which will look less intuitive and crowded, we want to group them into different groupings, for example, we only want to show the top 7 industries and group the rest of the Industry under the “Others” bucket.
Here, after we created the ranking function, before getting right to our grouping, we need to add the new Ranking and new Industry to reflect our top 7 and “Others” grouping.

result = foreach result generate ‘Date’, sum(Quantity) as ‘Quantity’
,case when ‘Ranking’ <= 7 then ‘Industry’ else “Others” end as ‘Industry’
,case when ‘Ranking’ <= 7 then ‘Ranking’ else 8 end as ‘Ranking’;

We utilize the ranking to get the top 7 industries, and the “Others” will just be number 8.

❗️Make sure your quantity calculation is always sum(’Quantity’) not first(’Quantity’).

The output has top 7 industries at the bottom from largest to smallest and Others at the top

Now you have your top 7 industries at the bottom from largest to smallest, and “Others” at the top.

I think this is usually the preferred case to separate “Others” from the top 7 industries. But if your client ever wants to mix them, all you need to do is to rank them again in your grouping. So instead of:

result = group result by (‘Date’, ‘Industry’, ‘Ranking’);
result = foreach result generate ‘Date’, ‘Industry’, sum(Quantity) as ‘Quantity’, ‘Ranking’;

Change it to

result = group result by (‘Date’, ‘Industry’, ‘Ranking’);
result = foreach result generate ‘Date’, ‘Industry’, sum(Quantity) as ‘Quantity’, rank() over([..] partition by ‘Date’ order by sum(Quantity) desc) as ‘Ranking’;

Now based on your data, your “Others” will be ranked together with the Top 7 industries – in our case, it is at the bottom since it is the largest quantity:

Others is also ranked with the Top 7 industries in the stacked bar graph

Bonus: Default Color

A quick reminder here – if you run into the issue of colors switching when you have different sets of companies, don’t forget you can always set the default color so they stay the same. This article explains it well. It is straightforward but helpful.

Change the Labels and Colors of Dataset Dimension Values

Enjoy SAQL!

Thought Leadership