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!

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!