Last and Prior Calculations: PART II

In my last post, I outlined one method for creating last and prior calculations using Tableau’s LAST() function. Although this is a pretty straightforward and useful method, it limits you in terms of creating table calculations such as percent change. In this post, I will show you another way to create last and prior calculations that will also allow you to perform table calculations.

Using level of detail calculations and IIF statements

The first thing you want to do with this method is create a calculation that will give you the last, or most recent, date in your data set. Using the sample superstore data, this would be something simple like:

{MAX([Order Date])}

By wrapping the expression in curly brackets, we’re essentially creating a fixed level of detail calculation. But in this case, because we haven’t set the scope, we’ve told Tableau to calculate the figure based on our entire dataset, thus giving us our most recent date. This will help when we do the DATEDIFF calculation in the next step.

Now if we go back to the table we created previously using the LAST() calculation, we can see that the most recent month in our dataset is given a value of 0. Again, as before, our last 6 months would therefore be months 0-5 and our prior 6 months would be months 6-12.

Screen Shot 2016-05-15 at 10.14.52 pm

Using this information and the most recent date (i.e. last date)  calculation, we can use the DATEDIFF function to generate our last 6 months that looks like this:

Screen Shot 2016-05-30 at 1.07.48 am

And for prior 6 months:

Screen Shot 2016-05-30 at 12.51.27 am.png

When we drag them into our table, we create a Boolean True/False calculation for the months in our dataset

Screen Shot 2016-05-30 at 12.51.45 am.png

In order to get the actual figures, we need to create another two calculations using an IIF statement. Because we cannot wrap our Sales figure in a SUM aggregation INSIDE the IIF statement, we can work around this by wrapping the SUM aggregation around the outside of the IIF statement like so:

Screen Shot 2016-06-04 at 12.34.27 pm

Calculation for last 6 month sales

Screen Shot 2016-06-04 at 12.36.27 pm.png

Calculation for prior 6 month sales

And now we can create a dual axis chart as I did in my previous post that compares the two figures:

Screen Shot 2016-05-30 at 1.00.49 am

Now the last thing we want to do with this method that we weren’t able to do with the previous method is create a percent change calculation. The calculation is as follows:

Screen Shot 2016-05-30 at 1.21.13 am

And now if we put everything together in a table, we can see our final result with our percent change calculation. This can be used to display percent change for any of our dimensions and the figures will update accordingly:

Screen Shot 2016-05-30 at 1.22.22 am

 

 

 

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: