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.).
