Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
)
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.
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
@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
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))
)
Thanks this worked perfectly!