8 Replies Latest reply on Jun 16, 2018 5:08 PM by Jalal Hassan

    [HELP] Percent Difference W/o Separate Worksheet.

    Jalal Hassan

      Hi,

       

      So I have been pulling my hair over this issue.

       

      I have a Date Parameter Calculation that allows users to compare period. The issue I am facing is that I need to introduce the % Difference between the two periods.

       

      While I do have a workaround in place it is not ideal.

       

      I've attached sample of how my data is structured, using the superstore data. It will be the last sheet.

       

      My issue is that while the superstore data only has 4 regions, I have 30+ regions I need to compare so scrolling is necessary, so it makes it difficult displaying the % difference for each region.

       

      Ideally I would like to find a way to display it all in a single sheet, the period comparisons and the % difference.

       

      Period Calculation

      IF
      DATETRUNC([Period Length],[Book Date])=
      DATETRUNC([Period Length],DATEADD([Period Length],-
      IIF([Period Comparison]=1,1,
      IIF([Period Comparison]=2 AND [Period Length]="day",365,
      IIF([Period Comparison]=2 AND [Period Length]="week",52,
      IIF([Period Comparison]=2 AND [Period Length]="month",12,
      IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
      IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
      AND
      DATETRUNC('day',[Book Date])<=
      DATETRUNC('day',DATEADD([Period Length],-
      IIF([Period Comparison]=1,1,
      IIF([Period Comparison]=2 AND [Period Length]="day",365,
      IIF([Period Comparison]=2 AND [Period Length]="week",52,
      IIF([Period Comparison]=2 AND [Period Length]="month",12,
      IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
      IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
      
      
      THEN
      
      
      "Period Comparison"
      
      
      
      
      ELSEIF
      (DATETRUNC([Period Length], [Book Date]) =
      DATETRUNC([Period Length], [Period End Day])
      AND
      DATETRUNC('day', [Book Date]) <=
      DATETRUNC('day', [Period End Day]))
      
      
      THEN
      
      
      "Current Period"
      
      
      
      
      END
      

       

      There is also a Period End, Period Comparison and Period Length Parameter.

       

      Capture.PNG

       

      Basically for my workaround I am hijacking the Grand Total column in a seperate worksheet and allowing the user to click on the region to show the % Difference.