0

# Calculated column for attributes in formula

Jan 27, 2017 at 07:18 PM

40

Former Member
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)

Former Member

Condition given above for reference..

condition.jpg (27.6 kB)

Florian Pfeffer
Jan 27, 2017 at 09:18 PM
0

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

Show 4 Share
Former Member

I applied this logic

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

and there is no error but the current date is not getting populated using CURRENT_DATE so now I need to figure how to get current date in calculation column.

Former Member
Former Member

TBP should only go with CLEAR_DATE = blank because if clearing date is there then the value should be LATE always

now even if the report viewed today then the amount is not cleared the value should be TBP (still payment needs to be done).

Former Member

The NOW function can be used to get the current date within a calculated column.

Regarding your additional condition for TBP: Just box another IF condition as third argument of the second IF function.

Former Member

Thanks Florian for your time ... really appreciate this!!