Custom Filter Logic

[mmaziere]

Hello,

I am attempting to identify ALL records where a customer file containing business hours (e.g. open from 09:00 to 17:00) per day of the week does not match the expected formatting. I have first created a scoring system via formulas per day of the week field:

Example scoring system for Mondays

IF [Monday] = '00:00:00' THEN 0 ELSE 1 ENDIF

IF [Monday2] = '00:00:00' THEN 0 ELSE 5 ENDIF

IF [Monday3] = '00:00:00' THEN 0 ELSE 7 ENDIF

IF [Monday4] = '00:00:00' THEN 0 ELSE 9 ENDIF

Each DOW has four possible fields to define business hours. Based on the math summarizing the four DOW fields, only 6, 10, 16 & 22 are acceptable scores. I am trying to create a file off of this that would identify all of the improperly formatted records. I attempted to then create a custom filter with the following criteria:

[M_Score] NOT IN(0, 1, 5, 7, 8, 9, 12, 13, 14, 15, 17,21) or [T_Score] NOT IN(0, 1, 5, 7, 8, 9, 12, 13, 14, 15, 17,21) or [W_Score] NOT IN(0, 1, 5, 7, 8, 9, 12, 13, 14, 15, 17,21) or [R_Score] NOT IN(0, 1, 5, 7, 8, 9, 12, 13, 14, 15, 17,21) or [F_Score] NOT IN(0, 1, 5, 7, 8, 9, 12, 13, 14, 15, 17,21)

However, this is not giving me the expected results (e.g. customers 565 and 659 are missing from the False results in the filter). Any suggestions would be helpful. Attached is an example of what I have so far.

[FinnCharlton]

Hi @mmaziere, 

It would be useful to know what your "expected formatting" is and why rows 565 and 659 do not match this "expected formatting".

[mmaziere]

The reason that i mentioned those two customer records, is that they have scores on certain days not matching with the 6,10,16 or 22 but are not in the False results: 

[FinnCharlton]

Ah, so the scoring system is working fine, but filtering out the incorrect rows is not working.

[FinnCharlton]

Hi @mmaziere , I would use this: 

[Christina_H · Accepted solution]

If you're doing what I think you're doing, you seem to be overcomplicating it with your scoring system.  Unless you need that for something else I would approach this completely differently.  Here's my take, which checks whether each customer has the same number of opening times as closing times each day.