- Bernard K
How to make WTD, MTD, QTD, and YTD calculations dynamic using a parameter in Tableau
In previous articles, I shared different ways to compute WTD vs PWTD, MTD vs PMTD, QTD vs PQTD, and YTD vs PYTD in Tableau.
In this article I would like to explore this subject further, by demonstrating how you can dynamically compute WTD, MTD, QTD and YTD using a parameter – in a single calculation (without writing several calculations for each period).
To do that, let’s first create a parameter named ‘Period’ as shown below.
(This string parameter contains a list of items namely, ‘week’ displayed as ‘WTD’, ‘month’ displayed as ‘MTD’, ‘quarter’ displayed as ‘QTD’, and ‘year’ displayed as ‘YTD’).
Create a filter calculation
Next create a calculation to filter the period or timeline being evaluated. Using the following calculation.
(Notice in this calculation I have simply replaced the date part with the parameter - enabling end-users to control which date part is applied in the computation).
Create a view
Create a view, in this case an area chart showing the Sales generated each day.
Add the calculation ‘Period filter’ to the filters shelf and select ‘TRUE’.
(This view shows the Sales generated each day between the start of the month and the current date (15/12/2022 – the day when this article was drafted) – Notice when you adjust the parameter from the current selection ‘MTD’ to other periods the axis will adjust accordingly).
E.g., See what happens when I adjust the parameter value from MTD to QTD.
(This view returns all transactions between the start of the current quarter to the current day – 15/12/2022)
Compute Change from Prior Period
To compute the percent change from the prior period. You can use the following calculation.
Change (%) = (Current Period (CP) – Prior Period (PP))/Prior Period (PP)
For quick follow-up – let’s breakdown these computations as follows.
(Notice in the above two calculations have replaced the date parts with the parameter ‘Period’)
Now let’s compute the percent change as follows.
(This calculation computes the percent change between the current period selected on the parameter and the prior period)
Now let’s show the percent change as a BAN, by adding ‘Current period (CP)’ and ‘% change’ calculations to the view as shown below.
Add the shape KPI to show the direction of change. Read this guide!
Combining the two views to create this dashboard – with which users can compute the performance based on the different periods by simply changing the value on the parameter control.
This could also be presented as shown below by leveraging parameter actions to control the parameter.
I hope this article was helpful to you. If you wish to receive more Tableau tips and tricks, kindly join our mailing list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!