Growth rate is usually a go-to metric when it comes to most datasets that would have any sales value. You can use them to compare two dates in time in order to evaluate the performance of what your looking at. These metrics can sometimes be seen as a ‘headline metric’ the big numbers that go on the front of any Viz to quickly show any viewer what they want to know asap.
Here’s how to go about making these, for the purpose of this example I will be making a YoY (Year over Year) comparison which will compare a current year of sales to a previous year of sales. Though this can be changed to whatever date comparison you will want and this will be explained how.
First you will need the current date, this can be done in several ways depending on your dataset.
{ MAX([Date])} This can be used to get the last date in a date dimension.
TODAY() This will get the date of today from your computer, though it is not recommended in-case your dataset does not update daily and will have daily data.
[Date Parameter] A date parameter can be used to make the following calculation more dynamic as the user can change which initial date to compare to.
Now that we have our starting date we can move on to creating the comparison values.
DATE(DATETRUNC('year',[Most Recent Date])) In order to capture a full year we will need the start point to our year. Using DATETRUNC we will get Jan 1 of the current year.
I call the above code “Jan 1 CY” and move onto making sales data that just captures the current year.
IF [Date]>=[Jan 1 CY] AND [Date]<=[Most Recent Date] What this code is doing is capturing all sales occurring after Jan 1 but before our set recent date so now we have our current year of data to compare from.
THEN [sales]
ELSE 0
END
We will now do the exact same process but for capturing the previous year of sales.
In order to get the Previous Year of sales we start with getting our set current date but move it back a year using DATEADD
DATE(DATEADD('year',-1,[Most Recent Date]))
Then again we get the Jan 1st point for that year.
DATE(DATETRUNC('year',[Most Recent Date PY]))
Now like before we capture all sales made in between those two dates
IF [Date]>=[Jan 1 PY] AND [Date]<=[Most Recent Date PY]
THEN [sales]
ELSE 0
END
Congratulations now we have two years of data to compare!
To get your year over year metrics simply minus one from the other.
SUM([CY Sales])-SUM([PY Sales])
To get it in a nice percentage then make this [YoY]/SUM([PY Sales])
In tableau right click the YoY % and go to default properties then number format, at the bottom click custom and insert the following.
▲0.0%;▼0.0%
This makes it look nice and have arrows for any user to quickly see if the sales are up or down!
If you wish to make any Month over Month calculations or even quarters then repeat the same steps but instead in the DATEADD calcs change ‘year’ to the value you want to compare to instead.
