How to rank 3 different results without using multiple If() statements?

[EderMachado1]

Hello to all QlikView gurus!

I'm currently building a simple chart using PickMatch dimensions and expressions, nothing out of the ordinary. There I'm calculating something called Lead Time using 3 different variables, all of them being pretty much the same sans the year inside the set analysis. What I'm trying to do is to compare them between each other, and assign a color to them depending on their rank: the lowest number should be green, the middle one yellow and the highest number red, and as an aside condition if all of them happen to be the exact same number then all of them should be green. The thing is, I have no idea on how to do this inside an expression other than using an unholy amount of nested If() statements which honestly doesn't sound like the right way to handle this.

Here, for example, all 3 should have a green LED indicator between rows since they're all the same number (15).

And here the 18 should have a green one, the first 19 a yellow one and the last 19 a red one.

The variables I'm using have simple names, they're called "vLeadTimeCap", "vLeadTimeCapA" and "vLeadTimeCapAA". As I said, I have no idea how to handle something like this other than using a lot of If() statements, I don't know if there's any kind of function similar to Rank() which lets me sort a set of parameters or something like that.

Is there an easier way to do this I might be overlooking?

[marcus_sommer]

It might be done without if-loops with something like:

pick(rank(YourExpression), green(), yellow(), red())

You may need to apply some additionally statements like the right rank-function and parameters as well as a TOTAL and maybe also a floor/num to ensure a numeric result from rank and/or a rangemin/max to limit the rank to n values. If you want to try this way you should go it step by step as normal expression and not within the color-area to see if the intermediated parts work like expected.

Alternatively with using if-loops or in combination with the above mentioned method might be to use interrecord-functions like before/after to access other cells within the table.

Beside this are you sure that you would really get an added-value with colouring each value. Quite often it disimprove the readability instead of helping the user.  

- Marcus