Tableau Master Challenge #3 – Solution

Home   / Tableau Master Challenge #3 – Solution

Calculating Measures in Prior Years

So you think you have the solution to meet the Master Challenge? Compare your solution with the solution in this blog and see if you solved it. Keep in mind, there are several ways to solve this challenge, but one of the simplest ones is used in our solution to the challenge.

Keep in mind the challenge constraints, which were:

The following requirements must be met:

  • Allow the user to select calendar year, or fiscal year for the calculation of “prior period”.
  • Allow the user to enter the date on which the fiscal year starts.

In addition, the following outcomes must exist in the solution:

  • Based on the selection of calendar or fiscal year, compute the sales for the same period in all four years of data.
  • Also based on the selection of calendar or fiscal year, compute the sales for the entire year.

To align with the solution, configure your workbook as follows:

  • Filter the data source to include data through May 8, 2013.
  • Set the default fiscal year to start on October 1st.

With the parameters of the solution defined, let’s go to the solution.

Toward the Solution

This solution includes several non-required, logical steps to help you arrive at the solution. If you want to skip straight to the solution and bypass the logical steps to get there, skip to the next section. Otherwise, the first step in understanding the logic of computing prior periods, is to start by defining the inventory of dates available in the source data.

Figure 1 organizes the dates based on the month and year. To achieve this two parameters must be created, as well as four calculated fields, as follows:

Parameters

Based On: String, with two options, “Calendar Year” and “Fiscal Year”.

Fiscal Year Start: Date, set to a value of October 1st (note the year is irrelevant).

Calculated Fields

Fiscal Start Month: MONTH( [Fiscal Year Start] )

Calendar Year: YEAR( [Order Date] )

Fiscal Year: [Calendar Year] + IIF( DATEPART( ‘month’, [Order Date] ) >= [Fiscal Start Month], 1, 0 )

Year: IIF( [Based On] = “Calendar Year”, [Calendar Year], [Fiscal Year] )

The calculated fields here do the following: calculate the month the fiscal year starts on, adjust the fiscal year based on what month it starts, compute calendar years, and return either fiscal or calendar year based on the user’s selection in the Based On and Fiscal Year Start parameters.

These calculations are then added to the view, as shown in Figure 1.

Figure 1

To better understand what we’re trying to achieve, let’s add a bit more structure to the dates, as shown in Figure 2, and highlight the latest date that has sales. In this case it is May 8, 2013, which is highlighted in orange. This step requires two additional calculations.

Calculated Fields

Latest Order Date: { MAX( [Order Date] ) }
Is Latest Date: IIF( [Order Date] = [Latest Order Date], “Latest”, “Not Latest Date” )

Figure 2

Note that size shelf in Figure 2 is not required for the solution. It is used to “trick” Tableau into using a discrete value in what appears to be a highlight table. This actually is not a highlight table, it is a bar chart in every cell. When the size of the bar chart is set to the average of “Number of Records” it is always a size of one, which makes all bars the same size. A bit of tweaking on the size shelf then makes it look like a highlight table. If you don’t implement this trick, you would have blue and orange font colours, instead of cell background colours.

Calculated Fields

First Day of Latest Year

IF [Based On] = “Calendar Year” THEN
DATE( DATEPARSE( “yyyy-MM-dd”, STR( YEAR( [Latest Order Date] )) + “-01-01” ))
ELSE
DATE( DATEPARSE( “yyyy-MM-dd”, STR( YEAR( [Latest Order Date] ) – 1 ) + “-” + MID( STR( [Fiscal Year Start] ), 6, 5 )))
END

Rebased Order Date

IF MONTH( DATE( DATEPARSE( “yyyy-MM-dd”, STR( YEAR( [Latest Order Date] )) + “-” + MID( STR( [Order Date] ), 6, 5 )))) >= MONTH( [Fiscal Year Start] ) THEN

DATE( DATEPARSE( “yyyy-MM-dd”, STR( YEAR( [Latest Order Date] ) – 1 ) + “-” + MID( STR( [Order Date] ), 6, 5 )))
ELSE
DATE( DATEPARSE( “yyyy-MM-dd”, STR( YEAR( [Latest Order Date] )) + “-” + MID( STR( [Order Date] ), 6, 5 )))
END

In Rebased Period: [Rebased Order Date] >= [First Day of Latest Year] AND [Rebased Order Date] <= [Latest Order Date]

Figure 3

Note that in this data, the first year is a partial fiscal year, but 2011 and 2012 are complete years, while 2013 is the “current” year, that has data through the final May 8, 2013. In Figure 3, matching dates in prior years are highlighted orange. The main logic that allows for prior period calculation is contained in the “Rebased Order Date” calculated field. The essential logic takes all prior year dates and re-computes them for the latest year of data, or the partial year. This is called “rebasing” the date, or switching it into the current year (whether fiscal or calendar). If the date falls within the year’s start date and the highest date, it is in a matching period.

The logic that is used to identify matching periods is then replicated to a SUM. When the date matches, the sales for the day are included. Conversely, where the date is NOT within a matching range, a zero is substituted for the actual sales for the day.

The Solution

In Figure 4 the “Prior Period Sales” uses the logic that matches on prior period dates, while the regular “Sales” measure contains the full year of sales. The computation will automatically adjust depending on whether calendar or fiscal year is selected.

Calculated Field

Prior Period Sales

SUM( IF [In Rebased Period] THEN
[Sales]
ELSE
0
END )

Figure 4

If we want to verify the dates that are included, we can add minimum and maximum dates to the view, as shown in Figure 5. The resulting “Prior Period Sales” measure is just a standard aggregation that can be used like any other measure. In Figure 5 the prior period sales have been included in two table calculations computing difference from and % change.

Figure 5

Putting the various pieces together, we create a visual to compare full year sales versus sales that match in prior periods, as shown in Figure 6.

To Summarize

The most important logic used in the calculation is to “rebase” the dates into the current year, and then compare them to the range of dates in the current year. For example, if we rebase February 22, 2011 to the current year, or February 22, 2013, then compare it to either the fiscal or calendar year, it is included. However, if we rebase September 2, 2012 to the current year, or September 2, 2013, it falls outside the “high” range for the year, which is May 8, 2013.

The mandatory items in order to achieve this solution include:

Parameters

  • Based On
  • Fiscal Year Start

Calculated Fields

  • Fiscal Start Month
  • Fiscal Year
  • Calendar Year
  • Year
  • Rebased Order Date
  • In Rebased Period
  • First Day of Latest Year
  • Latest Order Date
  • Prior Period Sales

The full workbook this solution is built on is available on Tableau Public here.

Once you get used to this logic it is very easy to use on an ongoing basis, but many Tableau users find it difficult to puzzle this out on their own. Hopefully this walk through will help you understand how to calculate this on your own.
For those that wanted to tackle the extra credit, feel free to compare your solution to the one in the workbook mentioned previously. Figure 6 shows the final extra credit visual.

Note there are other ways to calculate same period prior year, but this is a relatively easy one to get started with. Happy Tableau-ing!

Ask one of our Tableau Pros if you need help solving this Tableau challenge or any other assistance with Tableau.

If you’re interested in significantly improving your Tableau skills, we offer Tableau Training courses including a Tableau Master Class for  advanced  users.

Want to know more?

Contact us