Row Level Conditional Colouring in Pivot Table

[Vishal_Gupta]

Hi Fellow Qlik Members,

I have a requirement from customer that they want to see conditional colouring based on Row Level and not on Column level.

As you can in the above screenshot Zone A in Slab 100-250 is having a value of 17 but still showing red, whereas Slab 500-1000 is having value 4 with color assigned green.

Expressions:

Dimension: Customer Zone

Measure: Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code])

Background Colour Expression: 

ColorMix2((rank(total(Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code])
))/(noofrows(TOTAL)/2))-1 ,rgb(255,239,0),lightred('150'),lightgreen('150'))

Can anyone let me know what can i do?

[chrismarlow]

Hi,

Your ranking is only happening within the columns of the pivot table.

So >1000 they are all the middle green as all have same rank. 100-250 17 is red as is lowest in that column, 500-1000 4 is the bright green as is highest.

So I think below gives you the rank across all;

Aggr(Rank(total Count(Distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code])),COLUMNDIM,[Customer Zone])

(Where COLUMNDIM is the column dimension - not sure what that is).

But then you need to work out how to split this down into your three colours ... as this will run from 1-21 (or so).

Cheers,

Chris.

[vinieme12]

try below

=ColorMix2( (aggr(rank(Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code])),[Customer Zone],ColumnDIM)/ count(distinct total [ColumnDim])) ,rgb(255,239,0),lightred(150),lightgreen(150))

OR

ColorMix2( HRANK(Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code]))/ count(distinct total [ColumnDim])) ,rgb(255,239,0),lightred(150),lightgreen(150))

replace [ColumnDim]  with your column dimension name

[Vishal_Gupta]

@vinieme12  & @chrismarlow  Thanks for replying

@chrismarlow your expression doesn't seem to work even after some modification.

@vinieme12 your 1st expression is working for me but now the row level total is behaving a little strange

For Example 

[vinieme12 · Accepted solution]

try 

=if(rowno()<>0,
colormix2(hrank(Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code]),4,1)/NoOfColumns(TOTAL)
,rgb(255,239,0),lightred(150),lightgreen(150))
)

or

=if(rowno()<>0,
colormix2(aggr(rank(Count(distinct {<[Current Fiscal Year Total Sales]-={0}>} [Customer Code]),4,1),[Customer Zone],[ColumnDimHere])/NoOfColumns(TOTAL)
,rgb(255,239,0),lightred(150),lightgreen(150))
)

[Vishal_Gupta]

Thanks this worked perfectly!