topic Re: Row Level Conditional Colouring in Pivot Table in App Development

Row Level Conditional Colouring in Pivot Table

Vishal_Gupta — Thu, 02 Jun 2022 11:43:22 GMT

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?

Re: Row Level Conditional Colouring in Pivot Table

chrismarlow — Thu, 02 Jun 2022 17:06:00 GMT

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.

Re: Row Level Conditional Colouring in Pivot Table

vinieme12 — Fri, 03 Jun 2022 03:26:43 GMT

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))

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

Re: Row Level Conditional Colouring in Pivot Table

Vishal_Gupta — Tue, 07 Jun 2022 21:50:09 GMT

@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

Re: Row Level Conditional Colouring in Pivot Table

vinieme12 — Wed, 08 Jun 2022 02:07:34 GMT

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)) )

=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)) )

Re: Row Level Conditional Colouring in Pivot Table

Vishal_Gupta — Wed, 08 Jun 2022 08:39:23 GMT

Thanks this worked perfectly!