Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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