Skip to Content
avatar image
Former Member

Calculated column for attributes in formula

NET DUE DATE CLEAR DATE CURRENT DATE STATUS 1/1/2017 1/2/2017 1/27/2017 LATE 1/31/2017 blank/0000000 1/27/2017 TBP 1/1/2017 1/1/2017 1/27/2017 PAID

Hi Experts - I am trying to achieve this status based on if condition or Case if possible.

Curently I tried using if as below but its not working for 3 conditional values

if(("NETDUEDATE">="CLEAR_DATE" and "CLEAR_DATE"!='00000000'), 'PAID', 'LATE', if("NETDUEDATE"<"CURRENT_DATE" and "CLEAR_DATE"='00000000'),'TBP')

if I remove the 2nd line if condition it works but the TBP (To BE PAID) conddition does not work somehow so need some expert advice.

Thanks in advance for help related to this (using if or case also work)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 27, 2017 at 09:18 PM

    The IF function has only three arguments. You are using five. You have to box the second IF function call in the call of the first IF function.

    if(("NETDUEDATE">="CLEAR_DATE" and "CLEAR_DATE"!='00000000'),'PAID',if(("NETDUEDATE"<"CURRENT_DATE" and "CLEAR_DATE"='00000000'),'TBP','LATE'))

    Please check also if the condition for "TBP" is correct, because the current one (NETDUEDATE lower than CURRENT_DATE ...) does not match your example for TBP (in your example the net due date is not lower than the current date).

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded