How to change background color in pivot table by expression ?

[Anonymous]

Hi all,

I want to show top 3  in green and bottom 3 in red.

If ( sum ( sales) > .5, rgb ( 255,0,0,)) 

Which function do I use to return top 3 or bottom 3 ?

[Anonymous]

Hi Layla,

try:

if(rank(sum(Sales))<=3,rgb(255,0,0), if(rank( - sum(Sales))<=3,rgb(0,255,0))

RS

[Anonymous]

Hi Rodolfo,

It works but it only returns 3 values in top 3.

I have 4 values that are in the top3 . Any way to fix this ?

[Anonymous]

Hi Layla,

you mean that the 3rd and the 4th have the same sales amount ? so you want to color those (it doesn't matter how many elements in the dimension) who has the top 3 sales amount.

[Anonymous]

Hi Layla,

you'll need to use  set analisys, try this:

if( sum(Sales) >= min(aggr(sum({<dim={"rank(sum(Sales))<=3"}>}Sales),dim))

,rgb(255,0,0),  sum(Sales) <= max(aggr(sum({<dim={"rank( -

sum(Sales))<=3"}>}Sales),dim))  , rgb(0,255,0) )

RS

2017-12-05 15:31 GMT-02:00 Layla Mar <qcwebmaster@qlikview.com>: