Revision of IF formula

Jsdeloria21

Hi Guys,

 

So I have an issue with my IF formula, initially I was able to get the logic of this but the client decided to add a Contract Status (Full Cancellation). After that my count seems to be out in some months. So I need to adjust the logic but for some reason I am getting the correct results in some months but after inserting the Full Cancellation logic it is not counting correctly. Sample below.

1. Contract Status
2. Book Date
3. Commitment Date

Formula in Excel:

Initially this is formula

If(A=”Cancelled”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A))

Since adding a new status Full Cancellation my logic is off in some months.

If(A=”Cancelled” or A=”Full Cancellation”,IF(B=<>”,”Cancelled”,IF(C<>””,”Back Out”,”Fall Out”)),IF(OR(A=”Cancelled with Re-Contract”,A=”Recontruct”),A))

 

Is there a way to simplify this in my ANAPLAN formula? Below is my ANAPLAN formula (I know it is too much IF statement which is not advisable).

IF Contract Status = 'Contract Status (V)'.Cancelled OR Contract Status = 'Contract Status (V)'.Full Cancellation AND ISBLANK(Book Date) AND ISBLANK(Commitment Date) THEN 'Contract Status (V)'.Fall Out ELSE IF Contract Status = 'Contract Status (V)'.Cancelled AND ISBLANK(Book Date) AND ISNOTBLANK(Commitment Date) THEN 'Contract Status (V)'.Back Out ELSE IF Contract Status = 'Contract Status (V)'.Full Cancellation THEN 'Contract Status (V)'.Cancelled ELSE IF Contract Status = 'Contract Status (V)'.Recontract THEN BLANK ELSE IF Contract Status = 'Contract Status (V)'.Booked THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Fully Paid with OC THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.Reserved THEN Contract Status ELSE IF Contract Status = 'Contract Status (V)'.With Downpayment THEN Contract Status ELSE Contract Status

 

Appreciate if there is a much simplified formula for this.

 

Regards,

prabhu

Hi @Jsdeloria21 - Yes you are right, too much usage of IF Statement is not advisable.

in addition to that, you shouldn't use hardcode reference in any formula.

 

I tried to replicate your scenario.

 

I don't know your complete requirement, but my approach may give you an idea.

 

Step 1: I Created a Contract Status List.

 

 

 

Step 2: I Created a module (to highlight only cancelled and Full Cancellation), which help you in your 1st and 2nd statement of IF

 

 

 

Step 3: I Created a System Module with Contract Status as dimension.

Purpose of this module is to handle all other if and else portion in your IF formula

 

 

 

Final Formula Looks like.

 

 

 

 

 

Hope this may help you.

 

Thanks,

Sorna Raja Prabhu