How to identify last 3 values in qliksense pivot table and color it

[Karthick30]

Hello,

I am using total 4 fields. One field is for Pivot Dimension and 2 Fields as Columns and 1 field as Measure ( Sum(Sales)).

If I expand one of the column, I will get 7 values as sum(sales) . Is there any way to identify last 3 values and  assign color for last 3 values. Can Someone please advise.

-Karthick.

[jbhappysocks · Accepted solution]

Hi

You can add a color condition based on Secondary Dimensionality like below. But if your users start moving  dimensions and change format of the pivot I can't promise it will work

pick(SecondaryDimensionality(),white(),
pick(hrank(-sum(Sales),0,1),red(),blue(),green()))

[Karthick30]

Thanks @jbhappysocks  for your assistance on this. It worked Perfectly.

Can you please explain the functionality of this : pick(hrank(-sum(sales),0,1),red(),blue(),green()))

How it is calculating last 3 values even though we have not given 3 anywhere

[jbhappysocks]

Sure

syntax for pick is pick(n, expr1[ , expr2,...exprN]) and it is basically a neater if-expression:

if( n = 1, expr1, 

if(n = 2, expr2,

...

if(n = n, exprN)))

In this case n = hrank(-sum(sales),0,1)  and we specified 3 expressions = the 3 colors. So as long as the hrank gives 1-3 we get a color, but for the fourth rank there is nothing specified. Try adding a fourth color, you  can use rgb() or predefined colors ,and you will get you 4 ranked highlighted as well.

I would recommend you learn to use pick together with match, it is often much easier to work with than if.