SWITCH with multiple variables

[Chris_Bean1]

Hi folks

I have a calendar view of employee absences, with 'Leave' or 'Sick', and Leave split between Sign-off by management:Y/N. 

In the calendar, I have put in a VC to show the calendar entries in different colours with 

SWITCH(
[Sign-off by management],
“False”, “Orange”,
“True”, “Green”,"Blue")

This works, but I would like 'sick' records to show as black (or another colour like grey) in the calendar, so I tried AND with two separate SWITCH functions, but it didn't work. The Sick/Leave column is an enum type with those two options hard coded. Any ideas? 

Thanks in advance!
Chris

[WillowMobileSys]

I would use an IFS():

IFS(
    NOT([Sign-off by management], "Orange",
    AND([Sign-off by management], [Sick/Leave] = "Sick"), "Black",
    [Sign-off by management], "Green"
    TRUE, "Blue"
)

[Chris_Bean1]

Thanks! However I got a 'NOT has invalid structure: no more than one subexpression allowed. I tried wrapping that in brackets but no joy...

[WillowMobileSys]

Sorry, there was a parenthesis missing:

IFS(
    NOT([Sign-off by management]), "Orange",
    AND([Sign-off by management], [Sick/Leave] = "Sick"), "Black",
    [Sign-off by management], "Green"
    TRUE, "Blue"
)

Try this one 

[scott192]

You cannot make a value display 2 different colours.  You should have two separate Format Rules.  One to format the [Sign-off by management] value and one to format the Leave/Sick indicator (you did not say which column this is).

[WillowMobileSys]

---

@scott192 wrote:

You cannot make a value display 2 different colours. 

---

Not sure why you think there are 2 different colors being returned/applied.  Did I miss something?

The expression returns a single color based on criteria from 2 different columns.  It doesn't really matter which column the expression is applied to.  It could be a third column OR it could be one of the columns used in the expression.