How to get ‘This Month’, ‘Last Month’, ‘YoY’and a YTD trend line through a parameter, exclude LODs and some sneaky hiding

Within the space of 18 hours I was asked a similar question in Tableau. How do I get a series of ‘date based metrics’, and a 12 month trend table in the same view (WHAAAAAAAAAAAAAAA? thats possible? yes :)).

By ‘date based metrics’ I mean the type that are included in the title of this page, metrics like YTD, YoY, MoM, 12 month average, etc, etc, etc.

First things first, we need a parameter, which will contain a list of all the values that exist within our dataset. This will allow our users to select a specific ‘focus date’ with which all the metrics will adjust accordingly.

Can you do this without a parameter, so you always get just the comparison with the maximum month? You can try, but It adds complexity, and also removes the opportunity for your users to interact and see historically what has occurred.

I’m going to presume you know how to create a parameter, but if not then here is a handy guide from Tableau.


So now for the calculations. I’m actually going to start backwards here, and start with the YTD trend line (or table if you so wish).

The calculation to create our YTD trend line is simple:

IF

YEAR([Order Date]) = YEAR([Parameter Date])

//is the order date from the same year

AND

//2nd condition

[Order Date] <= [Parameter Date]

//is the order date less than the parameter date

THEN [Order Date]

//return the order date when true

END

//return NULL if the calculation is false

We are then going to start building our table, drag discreet month/year of our [YTD Dates] field to columns, and place sum of sales on text. What you will see is that we have 13 time periods, 12 YTD months (our parameter is set to December) and 1 NULL month. To get rid of this NULL month, simply right click on Null, and select ‘Hide’.

2016-10-27_21-04-20.png

The importance of using this method, rather than creating a TRUE/FALSE calc and placing this on the filter shelf is that to create a YoY, or 12 month average then we need dates from beyond the time period specified in our YTD calculation, so we must not exclude any previous time periods from being used in the view.

A few chart type changes and we can quickly end up with something like this…

2016-10-27_21-05-52


So now we are going to build out our ‘date based metrics’, in this example I am going to do YTD, This Month, Last Month and YoY difference.

YTD Calculation (essentially identical to our [YTD Dates] calculation, but this time returning [Sales] when true instead of [Order Date].

IF

YEAR([Order Date]) = YEAR([Parameter Date])

//is the order date from the same year

AND

//2nd condition

[Order Date] <= [Parameter Date]

//is the order date less than the parameter date

THEN [Sales]

//return the sales value when true

END

//return NULL if the calculation is false

Now I am going to drag this onto my view, on rows, between [Region] and [Sales] and make it discrete (to give me the value as text). What you will find is this actually breaks up our view because it is returning our YTD value for every different month that is in our view (so it’s giving us a row with each monthly value).

2016-10-26_21-48-56

Time for an LOD, because our value is being broken down by our [YTD Dates] field, we are going to EXCLUDE this field from our calculation. So our calculation now reads:

{ EXCLUDE [YTD Dates] :

//exclude the column YTD Dates] when calculating the below aggregation

SUM (

//all results of LODs must be an aggregation

IF YEAR([Order Date]) = YEAR([Parameter Date])  AND [Order Date] <= [Parameter Date]

THEN [Sales]

END

//return sales when months are YTD

}

//close LOD statement.

This will now leave us with one YTD value per row/Region. Now in order to remove the value SUM() from the title, we just change the pill to an attribute.

2016-10-27_21-07-07


This Month Calculation

{ EXCLUDE [YTD Dates] :

SUM(

IF

DATEDIFF(‘month’,[Order Date],[Parameter Date]) = 0

//Is the month/year of our order date the same as that given by our parameter

THEN [Sales]

//return sales when statement is true

END

//return null when statement is false

)}


Last Month Calculation

{ EXCLUDE [YTD Dates] :

SUM(

IF

DATEDIFF(‘month’,[Order Date],[Parameter Date]) = 1

//Is the month/year of our order date the previous to that given by our parameter

THEN [Sales]

//return sales when statement is true

END

//return null when statement is false

)}


YoY Calculation (by year over year I mean YTD this year verses this time last year)

{ EXCLUDE [YTD Dates] :

sum([YTD])  –

//the YTD value minus last years YTD value

sum(

//aggregate last year value

if

YEAR([Parameter Date]) – YEAR([Order Date]) = 1

//test if the year of the order date is 1 less than the year of the parameter date

AND

MONTH([Parameter Date]) >= MONTH([Order Date])

//test if the month of the order date is less than or equal to the month of the parameter date

then [Sales]

//if it is then return sales

END

//else return NULL

)}


Now we have all of our calculations, it is a case of dragging them onto the rows shelf, making them discrete and then attributing the values. Some formatting tweaks and we can get the finished product looking something like…

2016-10-27_21-09-10.png

The workbook can be found here.

Ben

#VizLikeAnArtist

Advertisements

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 )

Google+ photo

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

Connecting to %s