ColorMix in Matrix PivotTable

[markus_dreuw]

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?

[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]

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]

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]

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]

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 · Accepted solution]

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.