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.
Step 2 – Connecting to Data Source
Connect using Tableau to the newly created datasheet
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.
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.
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.
Similarly the second set “Trailing month2” is created for a five month trailing period.
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.
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.