3 Replies Latest reply on May 27, 2015 6:05 AM by Rebecca Kaye

Using colour to highlight top 3 within a stacked bar chart

Hello,

I have some data, which includes a time period, number of records (patients) and specialty. What I'm trying to do (but failing) is to create a stacked bar chart by time but use colour to highlight ONLY the top 3 specialties (over my entire dataset - not just within each time point) based on the first highest, second highest and third highest number of patients respectively.

In reality, I have hundereds of specialties so I went to make everything (outwith the top three) just one colour.

I'm sure this is easy and that I'm just thinking about it in the wrong way. I've tried using the index (but can't seem to add the 'number of patients' within the calculated field) and I've also tried the max functions but with no sucsess.

I've attached an example - in the attached workbook, the outcome would be that 'spec F', 'spec C' and 'spec A' would all have a different colour and the combined number of patients for all other 'specs' would be another colour.

Any help would be much appreciated.

Rebecca

• 1. Re: Using colour to highlight top 3 within a stacked bar chart

I've just figured it out.

Just in case it's of interest to anybody else - here's the attached solution (which involved sets).

Rebecca

• 2. Re: Using colour to highlight top 3 within a stacked bar chart

hi Rebecca,

Yes very nice solution, and sets are probably the easiest way to do this (btw if you move to Tableau 9, you can use LoD calculations to make this even easier!)

Here's a good link on how they work and what they do

Understanding Level of Detail (LOD) Expressions | Tableau Software

As I'd just got a solution for you, I thought I'd post it. I took the index() route, and the only thing you were missing (I think) was to 'sort' the index, so it acts as a rank.

If you look at the attached, I've created a Table version, so you can see how it works, and the final solution. One thing that is good with the sets method, is that a set is evaluated at line level (so you can then use the T/F of the set to create your colour formula), whereas with the Table Calc (index in this case) I have to leave the 'Specialty' in the detail of the viz (if you over over my bars, you'll see that I actually have every 'specialty' stacked, but as all but the top 3 are coloured differently you won't see them.

This is why LoDs are so good, they are evaluated at row level, but you can specify the level at why they are aggregated, and you don't need that level in the ViZ

• 3. Re: Using colour to highlight top 3 within a stacked bar chart

That's really helpful - thank you. The LoD functionality looks great too. I look forward to having a proper look when I upgrade.

Thanks again,

Rebecca