Trailing time period is a very useful feature for business to measure their performance over any specific time duration and most business users request this feature as part of their dashboard requirements. In this blog, we will be discussing one of the methods by which we can implement trailing time period in Tableau.

Data set considered for this is a 3 year month wise sales and profit information. (Jan-2011 to Dec- 2013)

Step 1 – Create a worksheet for sample sales
Create an excel sheet “Sample sales” containing 3 year month wise aggregated sales data.

sample-monthly-sales-data

Step 2 – Connecting to Data Source
Connect using Tableau to the newly created datasheet

 

connecting-tableau-to-spreadsheet

excel-workbook-connection-to-tableau
Step 3 – Create Visualization
Create a column graph using the data from the excel sheet. In this example, Sales vs time (date) is plotted as a column graph.
We have used a column graph for illustration purpose.

 

column-chart-tableau

 

Step 4 – Create Parameter
To create a rolling/trailing month feature, we require a parameter control to define the current month. To do this, we create a parameter using the date field.

Right click on the date measure and select “Create parameter” Option.

creating-parameter-in-tableau
In the edit parameter dialog box, enter appropriate values to ensure right control over the parameter. You can view the values that I have entered in the screen shot below.

edit-date-parameter-in-tableau

 

Step 5 – Creating “Dynamic set”
Next step is to create a dynamic ‘set’ on the date field. We create two such sets, which we will eventually use to filter out the required months for the trailing feature.

The first set will contain all values lesser than the value specified by the parameter control while the second set will contain all values above the -parameter value minus 5-. Therefore when we finally drag these two sets to the filters shelf, the sub set that remains after filtering is the required 5 month window which then dynamically changes with change in the parameter control.

To create a set, right click on the “date” field and select create set option. Since this is a dynamic set, we select the ‘condition’ tab and ‘by formula’ option over there. This opens up a ‘calculated field’ dialog box where you give the condition for the set.

 

create-dynamic-dataset-in-tableau

 

 


The condition for the first set is specified as follows.

set-condition

 

Similarly the second set “Trailing month2” is created for a five month trailing period.

trailing-month-condition
Syntax for second set is as follows:

trailing- month-condition
Note the small change in the second set condition. We have used the ‘dateadd’ function to reduce the months by 5 since we require a trailing 5 month feature in this case. This can be modified as per your requirement.

Step 6
Once the two sets are created, simply drag and drop the two sets into the filters shelf and now you have your trailing five month display. Use the parameter control to change the value of the latest month.

 

trailing-month-in-tableau

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com