Formulassss

[Hi2023]

Hi, 

How would I create a formulas for the below---I think I have it but want to make sure

1.

formula for 1 [LAST_PAY_DATE] within 12 months and [curr_bal subtract CR-LM] >=130 overlimit

2.

CR_BL >=0.9* CE_LM, CR_BL >=30, and [LAST_VL] is blank AND at least 2 [CONSTPAT] in 61 days or [CONNTE] = 2.

thanks

[Hi2023]

For the above formulas 1 and 2, can these be combined to be ONE  IF Statement with flags :

1 = True Out and 2= False In

[binuacs]

@Hi2023Wo you be able to provide some sample data and expected results, that would be very easy to create the formula,

[Hi2023]

IT is like the one you helped me create but I actually need this to be one formula IF statement

DateTimeDiff(DateTimeToday(),[LAST_PAY_DATE],'month')<= 12
AND  [curr_bal] - [CR-LM] >=130
***********if this is true to be flagged as TRUE ACTIVE OUTPUT


[CR_BL] >= 0.9 * [CE_LM]
AND [CR_BL] >=30
AND isEmpty([LAST_VL])
AND [CONSTPAT] >= 2 
AND (DateTimeDiff(DateTimeToday(),[LAST_PAY_DATE],'day')<= 61
OR 
[CONNTE] = 2) 
*********IF NOT THEN THIS TO BE flagged as NON ACTIVE Output

- 

[binuacs]

@Hi2023 you want this to be in a formula tool then you can write something like the below. Let me know if the formula works for you or not.

IIF((DateTimeDiff(DateTimeToday(),[LAST_PAY_DATE],'month')<= 12
AND  [curr_bal] - [CR-LM] >=130)
OR
([CR_BL] >= 0.9 * [CE_LM]
AND [CR_BL] >=30
AND isEmpty([LAST_VL])
AND [CONSTPAT] >= 2 
AND (DateTimeDiff(DateTimeToday(),[LAST_PAY_DATE],'day')<= 61
OR 
[CONNTE] = 2)),'Active','Not Active')

[Hi2023]

Thank you !

I tested it and it is picking up the 

AND isEmpty([LAST_VL])

I still have them populated -I think this is where the error is. 

That is a VString if it matters. Is there a better way to do that part?

[binuacs · Accepted solution]

@Hi2023 If the LAST_VL field has value then it will not pick since you are using the OR condition these records might be falling in the first condition. If the first condition is true it will not go to the next condition which is after the OR condition 

IIF((DateTimeDiff(DateTimeToday(),[LAST_PAY_DATE],'month')<= 12
AND  [curr_bal] - [CR-LM] >=130)