Nested IF Else Condition based Key Figure Calculation

[arindamdebnath]

I have a Key Figure calculation that checks period values and assigns the values of 3 Key figures according to condition fulfillment into a final Key figure.

The logic is as below:

if periodid2 - current period <= 11,

Disaggregation=disagg factor1

if periodid2 - current period >= 12 AND periodid2 - current period <= 23,

Disaggregation=disagg factor2

if periodid2 - current period >= 24 AND periodid2 - current period <= 35,

Disaggregation=disagg factor3

ELSE 0

I have written the below calculation for this:

TVADISAGGREGATION@MTHPRODLOCCUST = IF(("PERIODID3" - "$$PERIODID3CU$$") <= 11, "TVADISAGG1@MTHPRODLOCCUST",IF(("PERIODID3" - "$$PERIODID3CU$$") >= 12) AND(("PERIODID3" - "$$PERIODID3CU$$") <= 23),"TVADISAGG2@MTHPRODLOCCUST",IF(("PERIODID3" = "$$PERIODID3CU$$") >= 24) AND(("PERIODID3" - "$$PERIODID3CU$$") <= 35), "TVADISAGG3@MTHPRODLOCCUST",0)

The calculation validates successfully, but while performing CHECK on Planning area, it fails due to error: Function IF has incorrect number of parameters.

Where am I going wrong?

[chris_topf · Accepted solution]

Hello Arindahm,

it can be tricky when writing a calculation that has nested IF statements to get the syntax correct. I find it best to paste the formula in something that will highlight the matching (), an app like Notepad++ or Sublime. In any case, each IF statement needs to be enclosed in its own set of (). I pasted what you had into Notepad++ and added () to produce below, this should take care of the error during check. Also, there is a typo in the IF statement for comparing periods and be >= 24, you had an = between PERIODID3 and PERIODID3CU, it should be a '-':

IF(("PERIODID3" - "$$PERIODID3CU$$") <= 11, "TVADISAGG1@MTHPRODLOCCUST", IF((("PERIODID3" - "$$PERIODID3CU$$") >= 12) AND(("PERIODID3" - "$$PERIODID3CU$$") <= 23), "TVADISAGG2@MTHPRODLOCCUST", IF((("PERIODID3" - "$$PERIODID3CU$$") >= 24) AND(("PERIODID3" - "$$PERIODID3CU$$") <= 35), "TVADISAGG3@MTHPRODLOCCUST", 0)))

Regards,
Chris

[arindamdebnath · Accepted solution]

Thanks a lot Chris! This worked correctly, planning area is activated successfully and the logic also works as expected. You saved the day!