Your Data Over Time: Displaying YTD and Last Several Periods

Displaying cumulative total alongside the detailed view is a common requirement. Say you know what your profit for the current year (YTD) has been a few weeks ago but you are interested in seeing how those last  few weeks have impacted the overall picture. It is useful to look at the cumulative profit since the start of the year and how it has been changing over the past few weeks.

To get the cumulative total we can use a table calculation in Tableau. Now what if we want to view the running total as well as the detailed view but restrict the details to last few periods only?

Notice, if you simply apply the filter on dates (let's assume we want to look at last eight weeks), you will get a result similar to the one below.

Have a closer look at the screenshot above and you will notice the problem: our YTD Profit does not include any data prior to the 10th of November. But we know we have been making profit all year and we want to see that reflected in the overall YTD.

Here is the trick:

If you have done our advanced Tableau training you would be familiar with the fact that filters occur on the data source. This is the reason we run into the issue above. However, we know that table calculations happen directly in Tableau after all the data has been pulled from the data source. Consequently, if we use a filter on a table calculation, we will filter the data after it has been brought into Tableau.

We create a table calculation to retrieve the data from 'next eight periods'. Then we filter out everything where data for the 'next eight periods' exists (not null). This will only keep the last eight periods as we do not have any data from the future.

Here are the steps based on Superstore sample data:

1. Create the view with continuous week on Columns, Profit on Rows and another copy of profit on rows with the table calculation 'running sum'.
2. Add a third copy of the field Profit to Rows.
3. right click on the new copy and select 'Add table calculation'.
4. Select Difference from Previous Across the table.
5. In the Table Calculation pop-up select Customize.
6. Adjust the formula to "LOOKUP(ZN(SUM([Profit])), " Give it an appropriate name e.g. 'Next Eight Periods'.
7. Put the new field 'Next Eight Periods' on filter, select special 'null values'.
8. Remove the third copy of Profit from rows as we don't need it any more.

You can download and explore the result below:

Learn About Tableau