3 months ago by Joon Kim
Is it Apples-to-Apples?
A Year-on-Year (or YoY) change is conceptually a simple logic where a measure from the current period is compared to its equivalent from the prior year, often achieved by performing a simple table calculation that indexes specific point in the timeline.
However, as simple as it may sound, we may find at times a need to validate the comparisons we are making. Are we comparing apples to apples?
When we work a lot with dates and especially that of fiscal dates (i.e. weeks and years), we run into scenarios where the same periods between two fiscal years may not necessarily comprise of same number of weeks.
Below illustration highlights a scenario where a period from FY25 contains an additional fiscal week compared to that of the prior fiscal year. Under such a circumstance, it would be inappropriate to perform a comparison between sales amount that have resulted from 5 weeks’ worth of transactions in one year against only 4 from the prior year. To address the inequality, there is a method we could consider, which will ensure that the comparisons are consistently made between equal number of weeks per period.
Down-Top Approach
As tempting as it may be to simply compare Period-to-Period, different number of fiscal weeks present in some of the period pairs is going to produce misleading metric and that could have a detrimental impact on the final product.
As the nature of the problem boils down to the mismatching number of weeks, why don’t we start there and look at aligning the records at the weekly level first? Sounds like a good strategy? Then please keep reading.
Retrieve Measure Values at Weekly Level
Our first objective is to retrieve the measure values associated with those fiscal weeks that are 52 weeks prior, while making no reference to the period number or [Period End Date].
As depicted by the below diagram, the column [Sales Last Year] is being populated with sales values from 52 weeks prior. Note the first 4 fiscal weeks of Period 6 in FY25 have perfectly matching fiscal weeks from Period 6 of FY24, but upon reaching the [Week End Date] of 30/12/2024, there is no matching fiscal week from Period 6 of FY24.
Remember that we agreed to ignore the Period groupings while retrieving the weekly sales numbers? That is exactly what we will do and simply count -52 weeks from the week ending 30/12/2024, which will point to the sales figures of the week 7/1/2024.
Aggregate Measures at Period Level
Once the sales for the current period as well as the prior fiscal year are available side by side, we will roll up those numbers to the period level.
As a result, two additional columns will be required to hold the sum of sales for the Current Period (TY) and the Prior Period (LY).
Sales per Period TY
Sales per Period LY
Calculate YoY Change Across the Table and Chart
With sum of sales available for both fiscal years at a Period level, a simple percentage calculation across the table can be performed to determine the YoY change.
With all the necessary calculations completed, the final stage of the exercise is to convert the crosstab into a line chart displaying the trend in YoY changes.
The following section will detail the steps required in implementing this solution. Feel free to follow along while making reference to the sample workbook supplied here. https://public.tableau.com/views/YoYMismatchingFiscalWeeks/YoYTrend?:language=en-GB&publish=yes&:sid=&:redirect=auth&:display_count=n&:origin=viz_share_link
The Implementation
Step 1
As a start, create a worksheet with the dimensions [Period End Date], [Week End Date] and [Segment] in the Rows shelf and [Sales] to the Marks card in Text. When done, we should see a crosstab similar to the one in the image below.
Step 2
Next, create a calculated field [Sales 52 Weeks Prior], which will retrieve the sales from 52 weeks prior to the current dates in reference. To implement this, we will be use a table calculation function lookup().
Note: The offset of -52 defines the size of the interval from the current date that is being used as a point of reference. Being a negative value, instruction is to count 52 records backwards along the date range.
It is crucial that this table calculation is edited to provide a more explicit instruction on how -52 index should be counted in the presence of the 3 dimensions (i.e. [Period End Date], [Week End Date], [Segment]) present in the worksheet. As an example, it could by default be interpreted as counting simply 52 ‘rows’ backwards, in which case wrong sales values are retrieved. This is highlighted in the below image.
Next step will explain how the [Sales 52 Weeks Prior] should be arranged once created and inserted into the worksheet.
Step 3
Drag [Sales 52 Weeks Prior] into the crosstab and hover directly over where the current [Sales] values are displayed. This will generate a Show Me icon as per the image below.
Release the pill and [Sales 52 Weeks Prior] will be embedded into the crosstab as a new column.
As mentioned previously, it is critical that we setup the table calculation [Sales 52 Weeks Prior] correctly. To do this, right click on [Sales 52 Weeks Prior] pill in the Measure Values card and select Edit Table Calculation.
This will generate a pop-up menu, in which we need to update the setting as follows.
1. Select Specific Dimensions
2. Check Period End Date and Week End Date
3. At the level remains as Deepest (Default)
4. Restarting every remains as None (Default)
Note: Here is where we fine tune the way the table calculation is applied. Remembering that we used an interval value of -52 in the lookup() calculation, we are choosing to count 52 records backwards along the time line based on the dimensions
· Period End Date
· Week End Date
with no reference to the dimension [Segment]. By excluding [Segment] from the setting, we are stating that the lookup() function is to count backwards 52 records at [Week End Date] level. This is illustrated in the image below.
Once the table calculation edit is complete, we should now have confidence that the sales values shown in the column [Sales 52 Weeks Prior] is correct and accurate.
Step 4
With sales values available for the current year as well as last, we are now in a position to roll up the weekly measure values to the Period level.
Create a calculated field [Window Sum of Sales TY].
One of the key advantages of using the window_sum() is that this table calculation provides the flexibility to control the size of the window within which the measure values are summed.
Once the calculated field is created, drag and drop it into the crosstab by placing it in the Measure Values card. This will generate a third measure column in the crosstab.
Do not be alarmed at the sales value that is displayed initially.
The sales value shown in the newly added column by default represents the total sum of all the sales value present in the crosstab.
To address this, we will need to edit the table calculation as done previously for [Sales 52 Weeks Prior] by performing a right click on [Window Sum of Sales TY] pill in the Measure Values card and select Edit Table Calculation.
In the Edit Table Calculation, update the setting as follows.
Step 5
Repeat the same procedure from Step 4 for the sales from 52 weeks prior. To do this, create a calculated field [Window Sum of Sales 52 Weeks Prior].
As per the previous step, drag this field into the Measure Values card once created and right click on it to select Edit Table Calculation. In the pop-up menu, we will find there are two calculated fields being referenced.
Note: It is important to recognise that when a table calculation contains another table calculation within, selecting Edit Table Calculation will always highlight this as a Nested Calculation and it is always a good practice to check the setup for each calculation involved.
Ensure both calculations have the settings as per the above image.
Step 6
This represents the final step required to completing the crosstab with a calculation to determine the percentage change in sales from the prior fiscal year.
Given that our crosstab now contains the window sum of sales for the current period as well as that of the previous year, we are now able to perform the YoY change calculation by simply going across the table from left to right.
The calculated field required for this is [Sales Change YoY].
Once this calculated field is created, drag and drop the pill into the Measure Values card and right click on it to select Edit Table Calculation.
As was the case for all the table calculations of the prior steps, we will need to ensure that the nested table calculations have the correct settings.
In the nested calculations picklist, 3 table calculations will be present. It makes perfect sense to see these 3 in one place given that [Sales Change YoY] is created from them. The below image illustrates the construct.
In the Edit Table Calculations menu, click through each nested calculated field to check that the settings remain consistent throughout the workbook.
Note: From the screen captures of the Edit Table Calculation menu in Step 5, we observe that the settings are a little different between [Window Sum of Sales 52 Weeks Prior] and [Sales 52 Weeks Prior].
In the case of [Window Sum of Sales 52 Weeks Prior], [Period End Date] and [Week End Date] are checked the same way as it is for [Sales 52 Weeks Prior], but it has an additional instruction to restart at every [Period End Date], implying that the window sum is to be performed repeatedly for each Period in the timeline.
In the case of the [Sales 52 Weeks Prior], everything is the same with the exception being that there is no instruction to restart the calculation for each Period.
For our scenario, it is important to acknowledge that the restarting of the calculation is only required for those that are required to perform a window sum within the crosstab.
Please ensure these settings remain consistent in all the areas of the dashboard where these calculated fields are utilised.
Hot Tip: When dealing with large number of table calculations, it may be difficult to keep track of what settings to apply to which calculated field. To make it easier, try and create names that can be associated with the type of settings to be used. i.e. [Window Sum of …] vs those that do not require window sum to be performed.
Once all the steps above have been followed, we should by now have a crosstab that resembles the image below.
Let’s Draw the Chart
Step 7
Given the original objective was to plot the YoY change of sales for each [Segment] as a line chart and the fact that we have successfully calculated the desired values in the crosstab, we are now in a good position to use these results to generate the chart.
In this step, we will discuss the method used to convert the crosstab into a line chart.
Firstly, create a new worksheet and make sure that the Mark type is selected as Line.
Secondly, drag [Period End Date] to the Columns shelf and right click on it to select Exact Date. Then right click on [Period End Date] pill once more to select Continuous as the type. This will generate an X axis for the line chart.
Thirdly, place [Segment] field into the Marks card and place it in the Color box. We will come back to the colour assignment later.
Next, drag [Week End Date] field into the Marks card and place it in the Path box. Ensure that for [Week End Date], it is set to Exact Date and Continuous as its type.
Drag and drop [Sales Change YoY] field on to the Rows shelf. Then right click on the pill and select Edit Table Calculation.
Then in the Edit Table Calculation menu, repeat the process of checking the setup for each underlying table calculation and ensure they remain consistent.
By now, the worksheet should look like this image below.
Once the line chart is displayed in the worksheet similar to the image above, feel free to click on the Color box inside the Marks card to assign the desired set of colours.
Note: It is worth highlighting the fact that [Week End Date] was required to be present in the Marks card to allow the Edit Table Calculation step to be successful, since the pop up menu does require both [Period End Date] and [Week End Date] fields to be selected for its setting. While [Period End Date] is already present along the Columns shelf of the chart, [Week End Date] is a field that can easily be omitted since we are all focussed on generating the line chart at a monthly granularity.
Remembering that our monthly sum of sales was the aggregation of the weekly sales values, the table calculations will not work without the reference to the dimension [Week End Date].
It is also a common practice for developers to drop a field such as [Week End Date] into the Detail box of the Marks card to invoke its presence for the calculation, but doing so in this instance will result in the line chart becoming discrete points or dots across the axis.
To sidestep this undesirable effect, we would drop the [Week End Date] field into the Path box, which is an instruction to generate a continuous path between every pair of those data points and thereby generating a line chart.
It is also important to note that the path will determine the direction of the construct based on the natural order of the value that it references. In our example, it successfully connected the data points in the order of earliest date to the later date.
If the reference for the path instruction had been a non contiguous set of values such as Month numbers that would cycle through 1-12 then restart from 1, the line chart would not have generated correctly and instead could have resulted in erroneous connections between established between the data points.
Therefore, the hot tip is to always assign a dimension whose values have a definitive direction without being cyclical or containing repeated values.
Please refer to the workbook in Tableau Public here for the solution. https://public.tableau.com/views/YoYMismatchingFiscalWeeks/YoYTrend?:language=en-GB&publish=yes&:sid=&:redirect=auth&:display_count=n&:origin=viz_share_link
We use three kinds of cookies on our websites: required, functional, and advertising. You can choose whether functional and advertising cookies apply. Click on the different cookie categories to find out more about each category and to change the default settings.
Privacy Statement
Required cookies are necessary for basic website functionality. Some examples include: session cookies needed to transmit the website, authentication cookies, and security cookies.
Functional cookies enhance functions, performance, and services on the website. Some examples include: cookies used to analyze site traffic, cookies used for market research, and cookies used to display advertising that is not directed to a particular individual.
Advertising cookies track activity across websites in order to understand a viewer’s interests, and direct them specific marketing. Some examples include: cookies used for remarketing, or interest-based advertising.