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

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