Filter Pane with if Statement not giving the desired outcome

[MikeJones]

Hi, below shows the number of jobs. 

Date	Day	Total	InDay	OOH
21/01/2023	Sat	128		128
22/01/2023	Sun	18		18
23/01/2023	Mon	620	591	29

 InDay=Working Day and OOH= Out of Hours.  

Each job is determined to be InDay or OOH by the time they start it, though Weekend work is classified as OHH.

I am using two filters:

Filter 1 allows the user to determine what date they wish to examine.

Filter 2 allows the user to choose In Day or OHH.  Below shows the if statement

if([DateWeekDay]='Sat' or [DateWeekDay]='Sun', 'OOH',

if(OutOfHours='Yes' and match([DateWeekDay],'Mon','Tue','Wed','Thu','Fri'), 'OOH',

if(OutOfHours='No' and match([DateWeekDay],'Mon','Tue','Wed','Thu','Fri'), 'In Day','')))

(Note DateWeekDay=WeekDay(PlannedDate))

Below shows my output with OHH at 620 when it should be 29

	Day	Total	InDay	OOH
21/01/2023	Sat	128		128
22/01/2023	Sun	18		18
23/01/2023	Mon	620	591	620

[ogster1974]

My suggestion is to set your InDay/OOH Flag in your data model against each job start date then your filter on jobs will be applied correctly.

[MikeJones]

Hi Ogster, I should have mentioned that:

DateWeekDay=WeekDay(PlannedDate) is in Table 1, whilst OutofHours is in Table 2.

[ogster1974 · Accepted solution]

ok so again i'd look at the solution in your data model.

You're trying to identify if your job is out of hours or In day based on 2 criteria in separate tables.  

I'd create mapping tables to look up the criteria in both those tables then append them to your job table via ApplyMap()