Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markus_dreuw
Contributor III
Contributor III

ColorMix in Matrix PivotTable

Hi all,

I want to highlight values in a pivot table which is build as follows:

lines: different weight bands

columns: different destintations/zones

I currently use the following formula:

ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,rgb(0,70,155), rgb(204,224,238),rgb(242,242,242))

This one is only referencing on each single row.

I want it to reference on the whole matrix, so that eg. zone 5 / weight 301-400kg = 5 and zone 6 / weight 801-900kg = 5 are shown with the same background color.

Which expression do I need to use for that?

15 Replies
markus_dreuw
Contributor III
Contributor III
Author

Thank you. this is working.

One minor thing:

Now it is reffering to all weight bands existing in the data set.

When I filter on specific values, the colors stay the same.

Can that be adjusted as well? Maybe with {$}?

rubenmarin

If there is no set analisys, the default is {$}. Maybe I'm missing something but the only reason I see now is that the selected values includes the max cell value, if the value remains the same the % that assignes the color returns the same result.

But maybe you are looking for something based on rank like:

ColorMix1(Aggr(Rank(TOTAL Count([Shipment no]),1,1), D1, D2)/Max(Aggr(Rank(TOTAL Count([Shipment no]),1,4), D1, D2)), rgb(204,224,238),rgb(0,70,155))

markus_dreuw
Contributor III
Contributor III
Author

This only gives me the rgb(0,70,155) in all cells if I set

[Weight Band] for D1  and

[Zone] for D2

rubenmarin

Yep, should be a TOTAL in the max:

/Max(TOTAL Aggr(Rank(TOTAL Count([Shipment no]),1,1)


And maybe colors have to be switched.

markus_dreuw
Contributor III
Contributor III
Author

Thanks,

but with...

ColorMix1(Aggr(Rank(TOTAL Count([Shipment no]),1,1), [Weight Band], [Zone])/

Max(TOTAL Aggr(Rank(TOTAL Count([Shipment no]),1,4), [Weight Band], [Zone])),

rgb(0,70,155),rgb(256,256,256))

I get:

Columns 7 and 8 each show a "2", One is white, the other is blue-ish?!

rubenmarin

Best option is to split the expression in two, and check if the values in each part of the division are the expected values.

Parameters for Rank should be the same in both parts of the division. (1,1 or 1,4)