Formula function not filtering the data

[vtika16]

Hello,

I am trying to create a new field called "Servicer_Treatment" and setting multiple if then's to meet certain criteria based on the data that is provided in the formula function. Please see below.

IF Contains([Misc Invoice Treatment], 'PPO')
AND ([Misc Invoice Treatment Month] = '1-1-2017'
AND [Sublease Expiration Date] = '12-1-2016')
THEN 'Customer Purchase Option'

ELSEIF Contains([Misc Invoice Treatment], 'Received')
AND ([Misc Invoice Treatment Month] = '1-1-2017'
AND [Sublease Expiration Date] = '12-1-2016')
THEN 'Actual Devices Returned'

ELSEIF !IsEmpty([WriteOff_Date])
AND [Sublease Expiration Date] = '12-1-2016'
THEN 'Write Off'

ELSE 'NULL'

ENDIF

I checked that the data types were correct. Currently, the "Misc Invoice Treatment" field is set as V_WString. The "Misc Invoice Treatment Month" , "Sublease_Expiration_Month", and "Writeoff_Date" fields are set as date. When I run this, the "Servicer_Treatment" field returns all NULL's. Is there something that I am missing in this function? I also attached the workflow for further analysis. I added a bunch of browse and summarize functions to check each step, so don't worry about those too much :). Thank you!

[MarqueeCrew]

Try dates in this s format:

"2016-12-01"

Yyyy-mm-dd

[vtika16]

Tried it, still producing all the NULL's.

[MarqueeCrew · Accepted solution]

@vtika16,

Update after WebEx:

2016-21-01 was part of the problem.  That date should have been 2016-12-01.  The real issue was that the incoming data didn't appear to have the most recent month of activity.

We diagnosed this by taking the data into a summarize and looked at the combinations of available data.

I showed you how to define a user constant where you could enter the date for comparison once and modify your formulas to reference that date.  This way, you wouldn't have to update all of your formulas with the new date each month.

Another way to get there was to calculate the reference date as:

DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")

Cheers,

Mark