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.

[vikasmahajan]

Hi,

you can specify colors based on certain condition what is your condition for coloring ?

Vikas 

[Karthick30]

Condition is , I am using measure as Sum(Sales). Each column has 7 records. So out of that 7 records, I need to find only the last 3 values in each column. like 7th last is green() , 6th last is blue(), 5th last is grey() 

[MayilVahanan]

HI

Try something like below

Pick(Match(RowNo(),5,6,7)+1, black(), LightGray(), blue(), Green())

[Karthick30]

Thanks @MayilVahanan , But in your Example, for Feb Month , lowest 3 Sum(sales) is 100(A),200(B) and 230(H) . We need to give colors for those values only. 100 , 200 and 230

Also instead of row(in your example) , I am using column in my end. Do we have option for that?

-Karthick.

[Karthick30]

Any possible ways to achieve this please?

[jbhappysocks]

Hello

Use this to set your Background or Text Color:

pick(rank(-sum(Sales)),red(),blue(),green())

[Karthick30]

Hi @jbhappysocks ,

 I have used the same formula, but its not working . I am looking the result in row wise. 

For Ex: In first Row : 200000 ( min value ) - blue , 305368(2nd Min value) - green , 310000 ( 3rd min) - red().

All other fields should remain white.

[jbhappysocks]

If you want the rank by row in a pivot you use Hrank instead of Rank

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

Example above is not showing a blue cell since A and D are both ranked as 1 -> B is ranked as 3.

[Karthick30]

Thanks @jbhappysocks . One last question.Its perfectly working if I expand each columns like 1, 2, 3, 4. Inside each column i have checked , last 3 values color is fine. But Is there any way to show normal white background , if the columns are not expanded. 

Below screenshot , I have not expanded each column. but our background rule applied here as well. Do we have option to restrict that?