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

    Dynamic calculated field to get HoH based on Parameters date range


      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.



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


      (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


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


      Previous Year Current Quarter:

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


      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


      ELSEIF [Date Period] = "YTD" THEN

          IF [IsPY] THEN [Spend] END

      ELSEIF [Date Period] = "Selected Year" THEN

          IF [IsSelectedPY] THEN [Spend] END



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