How to create a Hierarchy for This Quarter to date over Prior Quarter to date Analysis that allows the user to drill from month in quarter to week to day in both quarters on a dual axis, maintaining comparative analysis. Today we will be using superstore data set!

What is Tableau Datediff?

DATEDIFF is a function that returns the difference between 2 specified dates as and integer. Example:

DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday')= 1
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday')= 0

The first expression returns 1 because when start_of_week is 'monday', then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when start_of_week is 'sunday' then 22 September (a Sunday) and 24 September (a Tuesday) are in the same week. With that, let's get started!

Quarter Over Quarter Comparison Guide

gif demonstrating Tableau functionality

Step 1: Setup the ‘Periods’ Calc

IF DATEDIFF('quarter',[tran_date],TODAY()) = 0 THEN "Current" ELSEIF DATEDIFF('quarter',[tran_date],TODAY()) = 1 AND DATEDIFF('day',DATETRUNC('quarter',[tran_date]),[tran_date]) <= DATEDIFF('day',DATETRUNC('quarter',TODAY()),TODAY()) THEN "Prior" END

Step 2: Make your Measure Components and KPI

Current $: IF [Periods] = "Current" THEN [Net Revenue] END

Prior $: IF [Periods] = "Prior" THEN [Net Revenue] END

Growth $: ZN(SUM([Current $])) - ZN(SUM([Prior $]))

Growth %: [Growth $] / SUM(ZN([Prior $]))

Growth KPI: IF [Growth $] > 0 THEN "Growth" ELSEIF [Growth $] < 0 THEN "Decline" ELSE "Stagnant" END

Step 3: Make your comparative date hierarchy

Month of Quarters

DATEDIFF('month',DATETRUNC('quarter',[tran_date]),[tran_date])+1

Week of Quarters

DATEDIFF('week',DATETRUNC('quarter',[tran_date]),[tran_date])+1

Day of Quarters

DATEDIFF('day',DATETRUNC('quarter',[tran_date]),[tran_date])+1

Step 4: Make the Viz.

  • Drag the Date in Quarters Hierarchy to Columns.
  • Drag 2 copies of Current $ to Rows, right click one pill and select Duel Axis.
  • Drag Growth KPI to Color Mark and Growth % to Label Mark.
  • On the 2nd Current $ Mark card, drag Growth KPI to Shape Mark (set desired shape, we used up and down arrows for growth and decline)
  • Adjust labels and color styles to your desired design specifications (synchronize axis, etc.).

DONE! Now Do This Trick Yourself

If you're looking for additional help, Zuar provides Tableau analytics consulting services! Save yourself time and hassle by working with our consulting team. Contact us to get started...