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

[binu_acs]

@Hi2023  can you re-arrange your formula like the condition and result, the above formula is confusing

[Hi2023 · Accepted solution]

Sorry-is this better?

[LAST_PAY_DATE] =1 and  within 12 months (Dynamic Date Filter)?

and  [curr_bal] (subtract) [CR-LM] >=130

Second Formula:

 [CR_BL] >=0.9* [CE_LM]

and [CR_BL] >=30

and [LAST_VL] is blank

and [CONSTPAT]  =2 or more

(date range within) 61 days OR [CONNTE] = 2

[binu_acs · Accepted solution]

@Hi2023 Based on the above condition the filter can be written as 

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


[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) 

[Hi2023]

TY!

[Hi2023]

For the date fields - it has nulls still in those columns even with a data cleanse tool. Do you know why? or how to remove?

[binu_acs]

@Hi2023 Do you have any condition to add in the formula for NULL Dates? I mean if the Date field is NULL what should be done? 

[Hi2023]

No, I do not. It should be blank but its only a few that null in those columns so I used a data cleanse but it didnt work.

[binu_acs · Accepted solution]

@Hi2023 Data Cleansing tool remove NULL values if the entire row or column is NULL else it will not remove. If you do not want those NULL values then add a filter tool before the Formula tool and filter out the Date field with Null values ( in the condition select the date field and "not null"  option from the drop down

[Hi2023]

But doesnt this remove this field altogther? I still need it to be ther e bc I need it for other formulas I have