Tableau — Dealing with Cumulative Data

Many times we get into a situation where we have to deal with cumulative data. Though it is possible to convert this data into its incremental nature easily (using various tools, python or excel), however, it might be in our interest to stay at the original level in order to better unlock the story. The purpose of this note is to discuss how to manage the Dashboard view when we are working with cumulative data — especially when we desire to build in the capability to zoom in zoom our using time metric as described in this note titled — “Tableau — User Selectable Time Metric”

An Example

Let’s say you are working on a time series that describes the capital and revenue expenditure of various GOI ministries. This data has two columns — one listing the budgetary estimate of a particular fiscal, and the other laying out the incremental financial spending over the fiscal year. The following picture provides a snapshot of this data for a chosen ministry.

Figure 1 — Sample Data (Revenue Exp of Telecom Dept)

In this data, column B lists the Budgetary Estimate (BE) for the FY, which might remain constant over the year or get revised, depending on the ministry’s performance. However, column C list the actual spending. This value keeps increasing over the fiscal and resets itself at the start of every new year. Due to the nature of this data working with incremental values will be less useful, as the story that we want to see will get diluted.

The Problem

When we feed such data in Tableau, we need to adopt a totally different approach while zooming in and out using the “Time Metric” parameter of “month, “quarter” or “year”. Why? As we can’t use any type of aggregation function (SUM or AVG). The reason is — These functions will lose relevance when applied to cumulative data, which also resets at the beginning of every fiscal. Here we will like to keep the data at the lowest level of granularity and use the time “Time Metric” parameter to pick data at only those time intervals that map to the chosen time metric. The following figure explains.

Figure 2 — Snapshot of monthly Rev Exp of Dept of Telecom

Can you see the problem? The data is laid out at the granularity of the “month”. But as soon as you pick “Time Metric” as “quarter”, you will like all the values within disappear — leaving only the columns markets “qtr” in red. The following picture explains.

Figure 3 — Snapshot of the quarterly view of Rev Exp of Dept of Telecom

In the above figure, only the months with the quarterly values stay visible, and the rest of the months disappear. A similar picture will emerge when you decide to view the early values at the end of every financial year.

The Solution

Now how do you program Tablaue to give you the required flexibility? It is actually not that difficult. We need to write the following calculated view to filter the view — without filtering the underline data.

Figure 4 — Calculation to Filter the View

This calculation is very simple to read. If we decide to choose “month”, then we want Tableau to display all the data within the frame of the defined viewing window.

Now if we decide to pick “quarter” then only display the months which define the end of that metric. Similar logic applies when you decide to pick “year”. However, there is one simple exception here. You also want the value of the latest month to also get displayed here, so as to give us a framework for comparing where we stand at the current month from the point of view of progress when compared with the previous year.

Defining the Window Width

Now we have to figure out a means to define the width of the window — i.e how many time units we want Tableau to display in the Dashboard. For this, we have to execute the following steps.

1. Define a Parameter “Time Units in View”. This parameter will allow the user to input the number of time units that we want on the Dashboard without causing the view to get cluttered.
2. Define a Parameter “Window Start Date”. This will be a list of all dates which is contained in the dataset. Doing so will allow us to Pan the whole window toward the left to get a view of the portion of data that we want to inspect.
3. Convert the “Window Start Date” into a dimension by simply embedding the parameter into the calculated field preferably with the same name.
4. Write a calculated field to define the “Window End Date” as follows — Date(DATEADD([Time Metric],-[Time Units in View],[Window End Date]))
5. Using the above calculation define the window width as below.

Figure 5 — Defining Widow Width

Note the calculated field “Time Frame” is the same as was described in my earlier note titled — “Tableau — User Selectable Time Metric”

The Final Worksheet View

The final worksheet view with all values plugged in is described in the following figure.

Figure 6 — Final Worksheet View

Also, the interested user can play with the final dashboard, which has all the controls placed on the top — providing the user with all the flexibility. Hope you find this useful. Thanks