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.

Customer_Time_Windows_Example.xlsx

New Workflow2.yxmd

Christina_H

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.

Business hours.yxmd