1 Reply Latest reply on Sep 13, 2018 2:09 PM by Patrick A Van Der Hyde

    Dynamic calculated field to get HoH based on Parameters date range

    roushankumar.srivastav.0

      Hi All,

       

      I need to create a dynamic calculated field to get half yearly calculation of numbers. My date range is dynamic and I am using parameters to get the date.

       

      eg.

      for some client year would be started from 1st April 2017 to 31st March 2018 ( 2017 H1 would be Q217+ Q317) and (2018 H1 would be Q417+Q118)

      for some 1st Jan 2017 to 31st December 2017 ( 1st half of year vs 2nd half of year)

      for some 1st October 2017 to 30th September 2018 ( 4Q17 + 1Q18 would be 2017 H1 and 2Q18+3Q18 would be 2018 H1)

       

      See below calculated file I have created for QoQ and YoY calculation. Just I need one more calculation to get HoH.

       

      Current quarter current year;

      YEAR([Current Date]) = YEAR([Selected Date]) AND

      DATEPART('quarter',[Current Date]) = DATEPART('quarter',[Selected Date])

       

      Previous quarter:

      (DATEPART( 'quarter',[Current Date]) >1 AND

      DATEPART('quarter',[Selected Date]) = DATEPART('quarter',[Current Date]) - 1 AND

      DATEPART('year',[Selected Date]) = DATEPART('year',[Current Date]))

      OR

      (DATEPART('quarter',[Current Date])= 1  AND  DATEPART('quarter',[Selected Date]) = 4 AND

         ( DATEPART('year',[Selected Date]) = DATEPART('year',[Current Date]) -1

       

      Previous Year;

      YEAR([Selected Date]) = YEAR([Current Date]) - 1

      AND

      MONTH([Selected Date]) <= MONTH([Current Date])

       

      Previous Year Current Quarter:

      DATEPART('quarter',[Selected Date]) = DATEPART('quarter',[Current Date])

      AND

      DATEPART('year',[Selected Date]) = DATEPART('year',[Current Date])-1

       

      I am getting previous spend based on below formula;

       

      IF [Date Period] = "Current Quarter" THEN

          IF [Comparison] = "QoQ" THEN

              IF [IsPQ] THEN [Spend] END

          ELSEIF [Comparison] = "YoY" THEN

              IF [IsPYCQ] THEN [Spend] END

          END

      ELSEIF [Date Period] = "YTD" THEN

          IF [IsPY] THEN [Spend] END

      ELSEIF [Date Period] = "Selected Year" THEN

          IF [IsSelectedPY] THEN [Spend] END

      END

       

      Please help to create a dynamic calculated file to get HoH. Thank you