Colour coded and parameter driven labels

One of the simplest but still one of my favourite Tableau tricks to add that little bit of extra insight into your visualisations is the ability to colour code your data labels.

Tableau doesn’t allow you to make certain labels go one colour and other labels another colour depending on a variable, so I’m going to show you how to get around it.

Note that this tip also used to apply to tooltips and can still be applied to them, but now you can embed worksheets into tooltips, that is an alternative way to achieve the same thing.

The example I always use is that I want to be able to create a label on a product which displays in one colour if the profit is positive and a different colour if it is negative.

This means creating two calculated fields, one which displays the profit, but only if it is positive, the other which just shows negative profit. Like this.

if SUM([Profit])>0 then SUM([Profit])
END

And

if SUM([Profit])<0 then SUM([Profit])
END

These both go onto the data label side by side, seeing as for each value it will be NULL for one calculated field, and a number for the other. snap 41You could then do whatever you liked within the tooltip for each of the fields, change the colour, the formatting, whatever you like. This gives a label which looks like this.

snap42So my negative profit items are flagged up in red and are shown in bold.

Getting parameters involved

That was the simple way of using it, but this technique of creating multiple fields to give the illusion of it being one field can be used in a variety of different applications. 

Like if I wanted the user to select whether they wanted to see profit in £ or in % terms, I can use the same method here.

First I create the parameter which will allow the user to pick whether they want £ or %.

I use a string parameter with a list, allowing either £ or %. Note I’ve used the “Display As” option to save me a bit of time when it comes to making the calculated field, but its still clear to the user what options I’m giving them.

snap43Then I modify my calculated fields from before to include a nested IF statement, so that we either show the £ or % value.

IF SUM([Profit])>0 THEN
    IF [£ or %]="£" THEN SUM([Profit])
    ELSE (SUM([Profit])/SUM([Sales]))
    END
END

Doing that to both my calculated fields gives a view like this.

snap44Note though that with this I can’t format my data labels to show a % if the parameter value is a %, and to show a £ if is a £.

So back to the drawing board? Well there are two ways out here:

The first and most boring is to change it to four calculated fields using AND function instead of the nested IFs, all stacked up next to each other, basically like this

IF SUM([Profit])>0 AND
    [£ or %]="£" THEN SUM([Profit])
END

but that is boring and I like a challenge so how do we do it all inside the two calculated fields?

Not for the faint hearted

The answer is to turn them into strings, rather than numbers.
The negative profit calculated field now becomes:

IF SUM([Profit])<0 THEN
    IF [£ or %]="£" THEN "-£" + SPLIT(SPLIT(STR(SUM([Profit])),".",1),"-",2)
    ELSE "-" + LEFT(SPLIT(STR(SUM([Profit])/SUM([Sales])),".",2),2)+ "%"
    END
END

This looks unnecessarily complicated but there is logic behind it.

First for the £. We first add a “-£” to the front of our number, and, working from the inside out, we turn the profit into a string. Now anybody who has done this before will know that will introduce floating point errors. That is, the number will appear like this £25.000000000001. To eliminate this I introduce a SPLIT, with the delimiter of the decimal point “.”, and taking the first split (as in the 25 bit of the number).

This then gives me, for negative profit numbers “-£-25.” Which is annoying, as I have two minus signs.

So to get rid of the second one which is the erroneous one, I introduce a second SPLIT, which this time uses a minus sign “-” as the delimiter, and because the number we want is after that minus sign, we take SPLIT number two.

Finally, that leave us with -£25. Note that you don’t need the second split or the minus sign for the [Positive Profit] calculated field.

So for the % bit. This works in a similar way to the £ section, so starting from the inside out first we have our profit % calculation, SUM([Profit])/SUM([Sales]), and again use the STR function to turn it into a string. Then to get the section after the decimal point I use another SPLIT function using “.” as a delimiter, and taking the second split we end up with something like 250000000001. Again we hit a floating point error so we simply take the two left characters, turning it into 25. Adding on the “-” sign if its a negative and a % sign at the end, we get 25%. Happy days.

Now our charts looks like this.

snap46snap45

I’m pretty happy with that. The final tidying up is if you want to get rid of the leading 0s on profits (positive or negative) which are less than 10%. This would simply be a case of checking if the first character is a zero, and if it is, chopping it off.

In my next post I will be showing you how to use the options with the label tab to show all labels at once, whilst highlighting the maximum value.

snap47

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s