If you’ve ever developed KPIs or BANs on a Tableau dashboard, there’s a chance you’ve also implemented color indicators for those numbers. Green being good, red being bad, and gray being neutral. This color indicator might also exist in your bar charts to show YoY changes or whether a goal was met for a specific metric. These indicator calculated fields can also be used to assign shapes like directional arrows, emojis, or any other shape. Whatever the case, there’s something I see beginners doing relatively frequently (and even some more experienced developers) that is additional work for no reason.
Using the Superstore data, it’s usually something like this:
Old Way to Color Things IF SUM([Sales]) - LOOKUP(SUM([Sales]),-1) > 0 THEN "Increase" ELSEIF SUM([Sales]) - LOOKUP(SUM([Sales]),-1) < 0 THEN "Decrease" ELSE "Neutral" END or IF SUM([Sales]) - LOOKUP(SUM([Sales]),-1) > 0 THEN TRUE ELSEIF SUM([Sales]) - LOOKUP(SUM([Sales]),-1) < 0 THEN FALSE ELSE NULL END
The first version should be avoided because it uses strings in the calculated field. Strings perform slower than integers and booleans and will start impacting dashboard performance the more complex your data and dashboards get.
The second version isn’t too bad (and the equivalent using integers as the resulting values), but still makes calculations more verbose than necessary.
So how you should you achieve this desired indicator functionality?
Using the SIGN function you can achieve the same results with a single line.
New Way to Color Things IF ZN(SIGN(SUM([Sales]) - LOOKUP(SUM([Sales]),-1)))
It automatically assigns a -1 for negative values, 0 for zero values, 1 for positive values and null for records that don’t evaluate. You can wrap the SIGN function around your entire calculation. On top of this, I would suggest wrapping that SIGN function with a ZN function like in the example. The ZN function will return the expression if it is not null, otherwise it returns a 0 value. Essentially this will eliminate null values from your SIGN function. Sometimes the null is desirable (you want to color specific marks that don’t evaluate) and sometimes you just want to color all null marks the same as zero.
Dashboard Example
The dashboard below is best viewed on a PC or tablet, but there is a mobile view for it as well.
What are the drawbacks?
Since you’re using a whole number as a data type, you can’t easily edit the aliases to make a human-readable string. Obviously making a human-readable string brings us back to the string data type which could negatively impact performance on large data sets. You could use the SIGN function calculated field in another calculated field to represent -1, 0, and 1 as your desired string. This isn’t the best to do either because now you’re nesting calculations and making the workbook a little more complex for the poor soul who inherits it in the future (very likely your future self).
One way to get around this is to just make a dummy legend that shows the color of the SIGN calculated field and what it pertains to (increase, decrease, steady). This won’t give a text value in the tooltip unfortunately, but that shouldn’t be necessary.
TL;DR
Use the SIGN function instead of IF ELSEIF functions to significantly reduce your calculation length. Wrap it with ZN if you only want three indicators (-1, 0, 1) or don’t wrap it with a ZN function if you want four indicators (-1, 0, 1, and NULL).
Pros: Simpler and less verbose calculated field, whole number data type for better performance than strings
Cons: Results in whole number so can’t directly alias
I hope this makes your development a little more efficient and enjoyable. If you enjoy the content, subscribe below to get notifications of new blog posts. I might do a giveaway soon, who knows…