Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

In the Tableau Desktop III: Advanced course, we teach you how to build sparklines, with the added bonus of using a dual axis to create a circle for the last sales value. This circle highlights that final point on the line, making it slightly easier to see. One of my participants wanted to take things to another level and focus on the last point in relation to whether it increased or decreased from the previous value. With well-chosen colors, this distinction can enhance your sparklines by quickly showing which dimension values might need more attention (e.g. areas that have gone down since the last time period).

“Can we color a point based on whether it went up or down compared to the previous point?”

The answer to this question is fairly straightforward. You just need to follow these steps:

1. Start with a dual-axis sparkline (one mark for the line, another for the points).
2. Create a calculated field to determine if a point is greater or less than the previous point.
3. Bring that calculated field to Color on the Marks card that creates the circle mark.

The chart in the Desktop III: Advanced course leaves us with a sparkline that looks like the one below. All the circles at the end are one color (orange), regardless of whether that value is higher or lower than the previous value.

To take this up a notch, a simple calculated field used on Color will color circles based on whether the last value went up (grey) or down (red) in relation to the previous value. Being thoughtful about color usage here means we will use a bolder color—red—to draw our users’ eyes to areas that need attention and minimize distractions with the softer grey. Which of these two chart options do you find more useful?

Here is the calculation to achieve this:

ZN() returns 0 if the Sum of Sales for a particular point is NULL. Otherwise, it returns our Sum of Sales. This is a form of error handling: comparing a sales value to NULL will yield NULL (which we don’t want), while comparing a sales value to 0 will yield TRUE or FALSE.

LOOKUP() is a table calculation that returns the Sum of Sales in a target column as determined by the offset, which is -1 in this calculation. With Compute Using set to Table (Across), that means looking one column to the left (to the previous month in this case).

Now, bring the calculated field just created to Color on the Marks card that is creating your circle mark. In our case, the sparkline is comprised of a dual axis: one mark for the line, the other mark for the circle. Since LOOKUP() is a table calculation, you can set the Compute Using options on this Up or Down pill to Table (Across) once you bring it on to Color:

Now click on Color to edit the colors for TRUE and FALSE. Consider which value you want to emphasize, and make that a bolder/brighter color, leaving the other value softer/lighter. Now you’ve got a great-looking chart with colored points that draw your viewers’ eye exactly where you want it to go!