Managing the view of a Tableau dashboard is important so that we do not get it cluttered with unnecessary data —especially the data that is used to process the view. Normally, simple filtering should have served the purpose. No? Yes, but not without driving the view clunky. Hence, we have to figure out a better approach — that can serve both purposes. That is the purpose of this article.
The Example
Let’s say you have time series data of CPI (Consumer Price Index) month by month. Now in order for us to calculate inflation we need to compare the current month's data with that of the previous year’s month using the following formula.
% Inflation = [CPI (CY’s Month) — CPI (PY’s Month)] / CPI (PY’s Month)
The way to do that in Tableau is to use the LOOKUP function. This function can be used to lay out the time series data on top of each other for a specified offset [LOOKUP(Sum(CPI), Offset)], as described in the following table.
One can choose a desired offset in the Lookup function, in this case, it has been chosen as 12. Doing that a portion of the time series data offset by 12 months is laid on top of the original time series data (shown in the center column). Now, since these two columns (the one just after the date, and the other — the middle one) are adjacent to each other, one can the simple formula described above for calculating inflation.
The Problem
An attentive reader can clearly see the problem at hand. The time series data is filtered on the dimension — Date and all values before 31st Jan 2021, are not available to the LOOKUP function for overlaying the values adjacent to dates after 31st Jan 2022. This gives a very clunky view of the window with half of the values missing — with NULL in the LOOKUP column and nothing in the last column listing values of the inflation. The actual view of the Dashboard will look something like this.
The Solution
For finding the solution to this problem we have to understand Tableau’s filtering order of operation. This is embedded in the following picture.
Note that Table Calculation Filters are at the bottom of the list. This means if we use a Table calculation to manage the view, then all the time series values will be available to the LOOKUP functions for it to calculate the inflation. The view will not get clunky and the user can filter the width of the view by changing the value of a defined parameter. Using the following steps one can resolve the problem.
Step 1 — Define a Parameter to Set the width of the view
Hence, let's define a parameter called “Time Units in View”. This one can easily to from the dropdown menu, as shown in the figure below.
Step 2 — Write a Calculation for filtering the view
I have named this as DateFilter described in the figure below.
One can clearly see what is going on here. I have used a dynamic Time Metric as explained in my earlier note — “Tableau — User Selectable Time Metric”. Also, see I have used a Window Function called LAST(). This function is nothing but a series of number that overlaps on top of our time series which tell us how far we are from the edge of the window. This is described in the following figure.
Now the calculation →” LAST()<Time Units in View” throws up a boolean output TRUE for all the Date values for which we want the time series data to be visible. And for the rest of the time series, this output will be FALSE. Now using this as a “filter” we can dynamically control the width of the view without letting the view get cluttered. Also Note that the formula for the time metric, “quarter” and “year” in the Data Filter has been appropriately modified so that the Window View always remains stable even when you decide to zoom out to get better visibility of the data. Here is how the final output looks.
You can see you have the flexibility of all the controls (month, quarter, and year) to drive the view, and yet the view remains stable to the desired width that you want for the Dashboard.
Hope you found this information useful. Thanks. More tips to follow.
