[ This is a guest post by Hans Romeijn. This is a followup post to my recent post on calculating YTD/YoY, QTD/QoQ, MTD/MoM and WTD/WoW. Hans shows a practitioner's approach to the calculation with performance in mind. The key is to replace conditionals by multiplications with pre-calculated indicator 1 or Null. This can accelerate the calculations significantly, especially when the data set is big.
The companion workbook courtesy by Hans is available for download.
Good implementation and fast speed do matter greatly for business users. ]
---
Recently a post from Tableau Zen Master Alexander Mou showed up in my timeline on LinkedIn. The post was about Simple calculations for YTD/YoY and other timeframes in Tableau and he ended the post with an invite to comment.
As this is something I work with on a daily basis I got curious and followed the link to his blog. It was a very clear step by step explanation. What I noticed that initially the conditions to calculate the timeframes are in the Sales calculation itself. My comment on Alexander’s post was therefore to separate both and work with a 1/NULL indicator.
There are many ways to work with time period based calculations. The way I always set up these calculations is the following:
1. First I create condition based period calculations for each time frame the business wants to look at. The output of these calculations is either 1 or NULL
2. I create a parameter to be able to select the necessary time frame
3. Create two indicator fields (Current Year & Last Year) based on the parameter
4. Create the actual sales calculations where sales is multiplied with the indicators
Setting it up like this gives me a couple of advantages, such as:
· All period based calculations can be re-used for other purposes then Sales, e.g. Quantity, Margin, etc.
· Any changes or bugs can be fixed in one place: the concerning time indicator
· As the final (Sales) calculation is based on multiplying, there’s minimum impact on performance.
As all data sources and models I worked with have a calendar table, it is even better to actually calculate these indicators in your backend. When working with very big data sets this is definitely preferred.
Working with 1/NULL indicators is not limited to time only. When you actually get the hang of working with indicators, you can use them for all kinds of purposes. I apply them with different kind off conditional calculations e.g. show results for commodity products or certain customer groups only. This way I can avoid using too many filters and I can also use the indicators in LoD calculations.
For now, I will describe how to set up time indicators calculated in Tableau.
Step 1 Condition based indicators:
For each time frame we’re going to create a separate indicator. Below the list of Timeframes I commonly use, including the calculations:
· YTD (Year to Date)
oCCY: IF DATEDIFF('year',[Order Date],TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END
oLCY: IF DATEDIFF('year',[Order Date], TODAY())=1 AND [Order Date] <= DATEADD('year',-1, TODAY()) THEN 1 END
· YTM (Year to Month)
oCCY: IF DATEDIFF('year',[Order Date], TODAY())=0 AND DATEDIFF('month',[Order Date], TODAY())>0 THEN 1 END
oLCY: IF DATEDIFF('year',[Order Date], TODAY())=2 AND DATEDIFF('month',[Order Date], TODAY())>24 THEN 1 END
· YTQ (Year to Quarter)
oCCY: IF DATEDIFF('year',[Order Date], TODAY())=0 AND DATEDIFF('quarter',[Order Date], TODAY())>0 THEN 1 END
oLCY: IF DATEDIFF('year',[Order Date], TODAY())=1 AND DATEDIFF('quarter',[Order Date], TODAY())>4 THEN 1 END
· QTD (Quarter to Date)
oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END
oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=4 AND [Order Date] <= DATEADD('year',-1 , TODAY()) THEN 1 END
· QTM (Quarter to Month)
oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=0 AND DATEDIFF('month',[Order Date], TODAY())>0 THEN 1 END
oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=4 AND DATEDIFF('month',[Order Date], TODAY())>12 THEN 1 END
· MTD (Month to Date)
oCCY: IF DATEDIFF('month',[Order Date], TODAY())=0 AND [Order Date] <= TODAY() THEN 1 END
oLCY: IF DATEDIFF('month',[Order Date], TODAY())=12 AND [Order Date] <= DATEADD('year',-1, TODAY()) THEN 1 END
· L12M (Last 12 Months)
oCCY: IF DATEDIFF('month',[Order Date], TODAY())>=1 AND DATEDIFF('month',[Order Date], TODAY())<=12 THEN 1 END
oLCY: IF DATEDIFF('month',[Order Date], TODAY())>=13 AND DATEDIFF('month',[Order Date], TODAY())<=24 THEN 1 END
· LQ (Last Quarter)
oCCY: IF DATEDIFF('quarter',[Order Date], TODAY())=1 THEN 1 END
oLCY: IF DATEDIFF('quarter',[Order Date], TODAY())=5 THEN 1 END
· LM (Last Month)
oCCY: IF DATEDIFF('month',[Order Date], TODAY())=1 THEN 1 END
oLCY: IF DATEDIFF('month',[Order Date], TODAY())=13 THEN 1 END
Important! Make sure to convert all indicator calculations to Dimension.
I use the following abbreviations:
· CCY: Current Calendar Year
· LCY: Last Calendar Year
· PCY: Prior Calendar year
· CFY: Current Fiscal Year
· LFY: Last Fiscal Year
· PFY: Prior Fiscal Year
Step 2 Create a parameter
For this example I’ve created a string based parameter with all the possible timeframes:
Step 3 Create the parameter based indicator fields
All the options from the parameter need to be brought back to 1 calculation for each year. This calculation looks like this:
Ind Period CCY:
CASE [Period Selection]WHEN 'YTD' THEN [Ind YTD CCY]
WHEN 'YTM' THEN [Ind YTM CCY]
WHEN 'YTQ' THEN [Ind YTQ CCY]
WHEN 'QTD' THEN [Ind QTD CCY]
WHEN 'QTM' THEN [Ind QTM CCY]
WHEN 'MTD' THEN [Ind MTD CCY]
WHEN 'L12M' THEN [Ind L12M CCY]
WHEN 'LQ' THEN [Ind LQ CCY]
WHEN 'LM' THEN [Ind LM CCY]
END
Ind Period LCY:
CASE [Period Selection]
WHEN 'YTD' THEN [Ind YTD LCY]
WHEN 'YTM' THEN [Ind YTM LCY]
WHEN 'YTQ' THEN [Ind YTQ LCY]
WHEN 'QTD' THEN [Ind QTD LCY]
WHEN 'QTM' THEN [Ind QTM LCY]
WHEN 'MTD' THEN [Ind MTD LCY]
WHEN 'L12M' THEN [Ind L12M LCY]
WHEN 'LQ' THEN [Ind LQ LCY]
WHEN 'LM' THEN [Ind LM LCY]
END
Important! Also these calculations should be converted to Dimension.
Step 4 Create the Sales calculations
The final Sales calculations will look like this
· Sales Period CCY: SUM([Sales] * [Ind Period CCY])
· Sales Period LCY: SUM([Sales] * [Ind Period LCY])
To make this all properly work when applying with e.g. months, it’s best you create at least two additional calculations:
1. For sorting purpose:
Date Sort Month: DATEDIFF('month',[Order Date],TODAY())
2. For Filtering purpose, that only the concerning Months are shown:
Ind Period Filter: ZN([Ind Period CCY])+ ZN([Ind Period LCY])
A basic Year over Year comparison could look like this:
With the Period Parameter you can now easily change the timeframe and evaluate results.
Feel free to leave comments. You can contact Hans Romejin for questions.
Click the image to download the companion workbook and the table of indicators that you may use in your workbook.
No comments:
Post a Comment