Nested IF Statement help

[dbfg]

Hi Guys,

I'm trying to do a nested if statement that basically shows whether or not a date is available to book. My dimension column has dates for every day in the year. This column that I am trying to get to work, basically needs to count the number of employees who have holidays booked on that specific date (or not).

So, if more than 3 GENERAL DRIVERs have booked any one day off work, then that date should return as UNAVAILABLE.

If more than 1 person has booked a day off that is NOT a GENERAL DRIVER, then the date should also return as UNAVAILABLE

If a date has less than 3 GENERAL DRIVERs booked off, and also 0 from any other department, then the date should show as AVAILABLE

I've tried to do it myself, this was all I could achieve with an error in expression:

if(count({<[Job Role] = {'GENERAL DRIVER'}>} Employee) > 3, 'UNAVAILABLE', if(count({<[Job Role] <> {'GENERAL DRIVER'}>} Employee) > 1, 'UNAVAILABLE',
if(count({<[Job Role] = {'GENERAL DRIVER'}>} Employee) < 3 and count({<[Job Role] <> {'GENERAL DRIVER'}>} Employee) = 0, 'AVAILABLE')))

Any help is appreciated

[Mauritz_SA · Accepted solution]

Hi there

For starters you can replace <> with =- in the set expression.

if(count({<[Job Role] = {'GENERAL DRIVER'}>} Employee) > 3, 'UNAVAILABLE', if(count({<[Job Role] =- {'GENERAL DRIVER'}>} Employee) > 1, 'UNAVAILABLE',
if(count({<[Job Role] = {'GENERAL DRIVER'}>} Employee) < 3 and count({<[Job Role] =- {'GENERAL DRIVER'}>} Employee) = 0, 'AVAILABLE')))

Something like this should work as well:

if((count({<[Job Role] = {'GENERAL DRIVER'}>} Employee) + Count({<[Job Role] =- {'GENERAL DRIVER'}>} Employee)*3)<3,'AVAILABLE','UNAVAILABLE')

Regards,

Mauritz