How to display the variances between dates in Tableau Software – Skill Pill Video

Data analytics gives us valuable information about our organizations and how they work. Data exploration helps us understand the changes that are taking place in our results and goals we have set. Once we begin to visualize the details in our data and understand the impact they have had on our results, we can say that our organization is a data-driven company. The information found in data analysis provides new insights to questions related to correlations or patterns in our organization’s activity. In today’s post we will discuss how to display variances between dates in Tableau Software and how this type of graph helps us to quickly and efficiently identify changes that occur from day to day or between longer periods of time.

When we want to identify the evolution of profit or sales over a certain period, data analysis highlights several useful insights. One of the most useful solutions to understand the results obtained and to make informed strategic decisions about our organization activity, is to identify the variances that have taken place over a period of time and to understand the causes that have determined those variances.

Display variances between dates in Tableau

Displaying variances between dates adds ease in identifying our results when analyzing measures such as sales, profit or number of units sold. This type of graph helps end users to observe possible patterns or trends that occur in their data and that influence in one way or another the results of a certain period. At the same time, displaying variances between dates represents a starting point in identifying decisions that must be taken to achieve the set goals.

Displaying variances between dates is actually a comparison of some measures that can be identified at the level of days, weeks, months, quarters or years. Such an analysis displays the type of evolution that our organization has registered over a period of time. Also, the display of variances between dates highlights the aspects we need to focus in our actions regarding the results we want. Analyzing variances between dates provides an image of our organization evolution and helps companies make the necessary decisions to adjust and improve certain aspects of their work.

Next, we will create a chart which display variances between dates to identify the sales variances percentage that took place from one month to another:

Step 1: Connect to data

→ In Tableau Desktop, connect to Superstore sample data provided by Tableau.

Step 2: Create the visualization

→ Right click on Order Date and Drag it to Columns and select Month Continous Date.

→ Add Sales to Rows

→ Change the Mark in Bar Chart

→ Drag Order Date to filter, choose Year Discrete Date and keep only 2019

→ Set Size of Bars to Maximum. Reduce the Worksheet Size.

→ Create a calculated field named Dummy Date with the formula:
DATETRUNC(‘month’,[Order Date])+15
Why 15? We want that the variance line to be between 2 months, therefore 15=an average of 30 days per month/2

→ Add the Dummy Date on Columns

→ Select Dual Axis and Synchronize it

→ Choose Gantt Bar on the second Mark

→ Right click on Dummy Date axis and Uncheck Show Header

→ Create a calculated field named Variance for variance between dates:
(ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), 1))*(-1)

→ Drag the Variance field to Size on the Gantt Bar Mark

→ Create another calculated field named %Variance:
(ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), 1))
/
ZN(SUM([Sales]))
*
(-1)

→ Create 2 other calculated fields:
Negative %Variance
IF [% Variance]<0 Then [% Variance] END
Positive %Variance
IF [% Variance]>0 Then [% Variance] END

→ Add Negative %Variance and Positive %Variance on Gantt Bar Label, next to each other, and color red for negative and blue for positive

→ Create another calculated field named %Variance Sign
CASE SIGN([% Variance])
When -1 Then ‘Negative’
When 0 Then ‘Zero’
When 1 Then ‘Positive’
END

→ Drag field %Variance Sign to color on Gantt Bar Mark.

By Sergiu Rotaru

Download Tableau Software