Showing Min & Max and Start & End in Tableau – 2 Methods

Blog | May 27, 2018 | By Vijai Narasimha

End Users are very keen to understand the time trend of the business. Chronological progression in time reveals a lot about the journey. As discussed in some of the other blogs, following every value is difficult, especially if the time series has a lot of fluctuations. That is the reason for end users to ask for the best values or a smoother curve like a moving average or a moving median which shows crests and troughs based on data points on either side of the point of interest as the time series progresses. But some end users just want to look at the highest value (may be the best value) and lowest value (may be the weakest value). At the same time, there will be requests to show what the start value was and what the end value is. Showing all the four values – Start, High, Low and End depicts a very good journey of data. How was our data when we started; when did we reach good heights; when did we fall below our thresholds and how did we end? All of these are very important, interesting and fun features on reports.

In this example, we will show the labels for all the 4 above mentioned data points on a Time Series on Tableau. There are different ways to achieve this. In this blog, we shall discuss 2 different methods. One method focuses on label card features. The other method uses Table Calculations along with the label card. The second method accentuates the visualization a little bit by making it attractive.

For this blog, we will be using the Sample – Superstore dataset with the Orders table.

Method 1

In this method, we will be using the Label card features (on the Marks shelf) combined with the concept of Dual Axis.

1) Create a Time Series chart that has a good number of data points. 30 – 50 data points. For this example, we are using Order Date as Month Continuous on Columns with Profit as Sum Aggregation on Rows.

For this example, even though the end user can see the 4 interested data points clearly, it might not be the same when the values are very close, especially with stock values where the fluctuation is very close. Also, one more thing to note is that Tableau has very powerful Tooltips and end users can use them to get some more information. But Tooltips cannot be used in pdf or images. So, we need to provide labels on these data points.

2) Use a copy of the Sum of Profit on the Label card on Marks.

But at this point, the label is added to all the data points on the view. So, we need to adjust the Label card settings.

3) Use the Label card and convert the labels to show only the Minimum and Maximum values.

At this point, we don’t have to focus on Scope. Scope feature will be useful only when we have multiple Dimension members on the view.

Next, we need to replicate the same features for the First value and the Last value.

4) Use a copy of Sum of Profit on the Rows shelf. This will split the Marks shelf into multiple components and settings from the first Profit field will be duplicated.

5) Convert the Label settings to Start and End values.

6) The last step would be to create a Dual Axis and Synchronize the two axes. This way, for the end user it looks like a single graph with distinct data points. If required, we can format the labels and work on some cosmetics.

Now, we can see that everything looks clean and tidy. The end user has the required features to arrive at quick conclusions.

Method 2

Though Method 1 has the features, we can improvise it or enhance it by adding some special features. We can indicate the specific data points with either Circle or Square or Shapes to add the “wow” factor. This way we are attracting the audience to focus on those data points because they are clearly more appealing than the others.

A quick recap of Helper functions:

For this method we will take support from Table Calculations and Helper functions. A more detailed explanation about helper functions is mentioned here

Assuming we have 10 data points, A – J.

a) Index – Starts from 1 and will increment by 1 until the last value in the view.

b) Size – Total number of data points on the view.

c) First – Starts from the left-hand side with a 0 and increments but on the negative scale. This way it is telling how far the current data point is from the start value.

d) Last – It is the opposite of First and read from the right-hand side. The last data point will be 0 and progress towards left with a positive value. This way it is telling how far the current data point it from the end value.

For all helper functions, like Table Calculations, we need to focus on Scope and Direction. By default, it will be Table (Across) if the Dimension is on Columns or Table (Down) if the Dimension is on Rows.

Some of the initial steps are same. Here also we will be focusing on Label card settings and Dual Axis features.

1) Create a Time Series chart using Order Date as Month Continuous with Profit as Sum Aggregation.

2) Next, we need a calculation to isolate only the Minimum and Maximum values. (**This is a verification step)

Here most of us make a mistake. We write a Formula for Min (Profit) and Max (Profit). These values will give the lowest and highest value for that Month. But what we need is the lowest and the highest values in this visualization. So, we need to write a formula for the window and Window Table Calculations always take an aggregated measure into account. The Rank() function will also work. But we will use the Window Calculation.

Here the entire view is referred to as the Window. The calculation logic says if the Sum of Profit on the visualization is the Lowest value in the visualization or if the Sum of Profit is the Highest value, then display only those and make everything else a Null value.

3) Next, we need some calculations to isolate the Start and End data points. We can do it in multiple ways. Two of them are represented here. We can choose one of them. (**This is a verification step)

Scenario 1: We have 4 years * 12 months = 48 data points. Index() = 1 will be the first data point and Index() = Size() will be true only for the last data point.

Scenario 2: We have 4 years * 12 months = 48 data points. First() = 0 has to be the first data point. In the same way Last() = 0 has to be the last data point.

4) Next step would be to combine all the above into one calculation.

5) Next steps would include Dual Axis, Synchronize, Hide the Nulls and Formatting. We can also work on cosmetics by changing color for the line and data points to make it a little fancy.

The second visualization can be circle or square or any shape of choice.

Some more examples using the same method are here: