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?

1 Solution

Accepted Solutions
rubenmarin

Try with:

colorMix1(count([Shipment no])/Max(TOTAL Aggr(count([Shipment no]), [Weight Band], [Zone])),rgb(0,70,155), rgb(204,224,238))


Maybe colors have to be switched.

View solution in original post

15 Replies
rubenmarin

Hi Markus, I think it's because the "noofrows(TOTAL)" it's returning a different value by column, can you check adding it to a new expression?


In that case maybe you can try with "Count(TOTAL FieldName)" or similar that returns the same value in all cells.

markus_dreuw
Contributor III
Contributor III
Author

Hi,

with Count (TOTAL ...) I get the followin:

Unfortunately not what I need

rubenmarin

I don't know why it's not working, can you share a sample?

Or maybe creating a variable with "=Count(FieldName)" and the expression "$(VariableName)"

markus_dreuw
Contributor III
Contributor III
Author

I am not allowed to share due to comapny policies...

the formula for the above shown screenshot is:

ColorMix2( (rank(total column(1))/(count(TOTAL [Shipment no])/2))-1 ,rgb(0,70,155), rgb(204,224,238),rgb(242,242,242))

However, I rearranged the formula as follows:

ColorMix2( (rank(total count ([Shipment no]))/(Count( [Shipment no])))-1 ,rgb(0,70,155), rgb(204,224,238),rgb(242,242,242))

this is giving me

This looks better now bit still there are different colors for same values (e.g. 3) and the higher values all have the same color...

rubenmarin

And what's the calculated expression? the one that shows the number is also a rank expression?

markus_dreuw
Contributor III
Contributor III
Author

I think I need the rank for the ColorMix2 formula.

The calculated expression is the number of shipments or data sets.

Like in an excel pivot table I have e.g. total amount of shipments = 12.830

Filling in the lines, I will see the amounts per weight band.

Adding the Zones as well, I see how many shipments I have per zone / weight band combination. I total they add-up to 12.830.

Maybe the formula stated in the beginning is wrong.. that was just my first unsuccessful try...

rubenmarin

Maybe calculating the percentaje as:

[Shipments of Cell]/[Max Cell value]

For Max cell value you can use aggr:

Max(Aggr([Expression for shipments of cell], Dimension1, diemnsion2...))

You can use a variable to store the expression and use this variable in expressions.

Also, colormix2 is used when you want to use 3 colors using values from -1 to 0 to 1, if there will be no negative values, for going to light grey to blue you can use a simpler ColorMix1:

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/ChartFunctions/ColorFunctions/...

markus_dreuw
Contributor III
Contributor III
Author

ok, ColorMix1 makes sense for sure.

not sure how I should build the formula then.

This

colorMix1(  Max(Aggr([Shipment no], [Weight Band], [Zone]) / count([Shipment no])),rgb(0,70,155), rgb(204,224,238))

is not giving a usable solution.

rubenmarin

Try with:

colorMix1(count([Shipment no])/Max(TOTAL Aggr(count([Shipment no]), [Weight Band], [Zone])),rgb(0,70,155), rgb(204,224,238))


Maybe colors have to be switched.