Last and Prior Calculations: PART I

When working with performance metrics, it’s always a good idea to have some benchmark to compare against. It might feel good to say you made 100M in sales, but depending on the company, 100M might be a dramatic dip compared to the 200M you made last month. Other benchmarks might be competitor performance, figures from the same time last year, or figures from the same company but a different department.

Another benchmark that can be informative is comparing performance in the last x number of months versus performance in the x number of months prior to that – hence, last versus prior. In order to calculate these metrics in Tableau, you can do one of two tricks. In this post I will walk you through the steps to create these calculations using Tableau’s LAST() function.

USING LAST()

The first trick involves using the LAST() table calculation in Tableau, which assigns a value of 0 to the last row visible in your workbook. So if we are looking at our sales by quarter, our LAST() calculation would assign values like this:
Screen Shot 2016-05-15 at 10.12.56 pm

If we were looking at monthly sales, LAST() would assign values like this for our bottom (i.e. last) rows:

Screen Shot 2016-05-15 at 10.14.52 pm

Let’s say we wanted to see sales values for the last 6 months. All we would have to do is filter to the bottom 6 rows, which we can see are assigned values 0-5.  The calculation would look like this:

Screen Shot 2016-05-15 at 10.20.04 pm

And we would just drag that onto the filters shelf and select True. Now our table shows us sales for the last 6 months:

Screen Shot 2016-05-15 at 10.20.55 pm

Great. We’ve got sales for the last 6 months, and now we want to compare these to sales for the 6 months before that (i.e. prior 6 months).  If we look at our original table, we can see that the prior 6 months are assigned values 6-11 by the LAST() function:

Highlight

So using AND, we can obtain the prior 6 months with  a calculation like this:

Screen Shot 2016-05-15 at 10.25.56 pm

And now our table looks like this:

2016-05-19_09-10-26

In order to create a view that compares last and prior figures side by side, we can create a dual axis bar chart. First, using the previous Boolean calculation we made, create a new calculation that will display only Last sales and that looks like this:

IF [Last 6 Months] THEN SUM([Sales])
ELSE NULL
END

Do the same thing for a calculation that will display only Prior sales:

IF [Prior 6 Months] THEN SUM([Sales])
ELSE NULL
END

Now you can construct your dual axis bar chart. Drag your date field onto the detail shelf and set it to continuous MONTH. Set your mark type to bar. Then drag both calcs onto the columns shelf, and set them to compute using the date field. Click the arrow on one of your calculation pills and select dual axis, then right click on the axis at the top and synchronize your axis. Lastly, drag Measure Names from the Dimensions pane onto the rows shelf and you will have a dual axis bar chart showing you the values for sales in the last 6 months vs prior 6 months.

2016-05-19_09-13-54

Now, the problem with this method is that if you wanted to calculate % change between last and prior 6 months, you might have some difficulty. This is because your last and prior calculations are table calculations that cannot be aggregated.  In other words, the calculations are computing values for each month in the view, and they cannot be summed together in a calculation like SUM(Last 6 Month Sales) or SUM(Prior 6 Month Sales).

One option is a level of detail calculation to force the calculation to ignore the months in the view. Ah, but here’s another problem, you cannot combine table calculations and level of detail calculations. So what do? In my next post I will show you an alternative way to create last and prior calculations that will still allow you to use them in aggregate calculations such as % change.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: