Loading...

Our Recent Post

Instructions to Color Entire Tableau Charts Based on Latest Performance

Instructions to Color Entire Tableau Charts Based on Latest Performance

Since the stock market in recent weeks has been so unpredictable, we’ve seen plenty of sparklines showing the daily results for a symbol or index. The performance is also broken down by minute, but when the overall daily performance is negative, the entire chart is usually colored red and if the overall performance is good, then colored green.

Although I wish those charts had to say the stories were more optimistic, I really like the look of these charts and I wanted to recreate them in Tableau. In the past I’ve shown you how to highlight the latest trends, but it’s trickier to highlight a whole chart based on the latest trends because the default way data is aggregated in Tableau. This post teaches you how to use level of detail expressions to get around the order of operations of Tableau in order to highlight whole charts based on a single value.

How to color whole sparklines according to the most current value

You will be able to color each row (or column) in a chart by the end of this article, based on whether the current output has been positive or negative.

This method works for every continuous part of the date like Year, Quarter, Month, Week, Day, Hour, Minute, or Second. This means that you can only replicate the stock ticker sparklines mentioned in the introduction, but you can use the Sample-Superstore dataset to follow along, we’ll begin with those sparklines that see at Sales by Region by Month.

Let’s assume for each region we want to color the sparkline blue when the last month over month change was positive and red when the last month over month change was negative.

The strategy needs only three calculated fields. You can combine all three into only one, but I will build all three because

  1. I would like to show you each component individually
  2. the calculated fields are easier to handle / troubleshoot
  3. this enables you can use the independent calculations for other things.

First calculated field inhibits in the dataset the lastest month. Also this fits for any part of the date, so simply substitute ‘month’ ,when you are using the date part, in my calculation. I do use the Sample-Superstore dataset, so the date field name is “Order Date.”

{MAX(DATETRUNC(‘month’,[Order Date]))}

Second calculated field in the dataset separates the second to last month. This is done with the function DATEADD by adding the month “-1” to the MaxMonth. If you have a date part other than month, you’d replace ‘month’ once again with the date part you’re using.

The final third calculated field decides whether the lastest month has exceeded the second to last month. It would be a Boolean calculation, which will ultimately coloring the lines, one color when the result is “right” and the other color when it is “false.” The sparklines view analyzes the Sales measure, so the calculation will be:

{EXCLUDE [Order Date]: SUM(IF DATETRUNC(‘month’,[Order Date]) = [MaxMonth]

THEN [Sales] END)}

{EXCLUDE [Order Date]: SUM(IF DATETRUNC(‘month’,[Order Date]) = [MaxMonth -1]

THEN [Sales] END)}

This one sounds more complex, but all it does is look at each date on the sparkline to decide if they match either the Maxmonth or the second to last month. When those two are separated, the formula decides if sales of MaxMonth are greater than the second to last month. At the beginning, the EXCLUDE level of detail expression is what allows the color to be displayed along each date, irrespective of whether it is the Maxmonth or the second to last month.

Let’s place the third calculated field onto the Color Marks Card.

When the last month surpassed the previous month, the whole line is colored blue; the whole line is colored red when the last month below performed the previous month!

Even while using a date filer which is dynamic, there’s one whoop.In our first calculated field (in my case MaxMonth) utilizes a FIXED level of detail expression, the last month, in the overall dataset, will always be separated; not the lastest month in the filtered context.This means we’re stuck looking at the performance of December 2019 against the performance of November 2019 in the Sample-Superstore dataset.

However, since the level of detail expressions is defined after context filters in the order of operations in Tableau, we can apply the date filter to the context to have the last month calculated by the range filtered. We can add a filter to context, by right clicking it on the Filters Shelf and select “Add to Context”.

Let’s see how the view looks after inserting a date filter to the Filters Shelf, implementing the date filter to context, and selecting between 1/1/2018 till 4/30/2019.

Although using sparklines as an example, this technique works for many other chart types. Let’s see how the view looks after converting the sparklines into a dual-axis combination chart with a mark type of Line on one axis and a secondary axis as mark type of Area!

  • Posted On: 20 Apr 2020
  • Posted By: cognitive
  • Share :

Related Post

Presenting Pace Charts in Tableau

Presenting Pace Charts in Tableau

Pace charts is an innovative bullet graph design that normalizes development of target visualizations through KPIs, though the KPIs have extraordinary information seasonal trends, formats and/or scales…

Know More
Data Pre-processing – Encoding with applymap in Pandas

Data Pre-processing – Encoding with applymap in Pandas

Data pre-processing is one of the important steps as it is not only time consuming but also critical for any business if we think about predictions.

Know More
Categories
Keywords

Have any Question? Call Us..

(+ 91) 9356237404

For Details