topic Re: ColorMix in Matrix PivotTable in App Development

ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 07:55:34 GMT

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?

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 08:17:33 GMT

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.

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 08:34:04 GMT

Hi,

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

Unfortunately not what I need

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 08:43:02 GMT

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

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 09:42:20 GMT

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

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 09:53:28 GMT

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

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 10:02:45 GMT

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

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 10:12:10 GMT

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/color-functions-char…

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 10:41:42 GMT

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.

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 10:44:12 GMT

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.

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 11:38:35 GMT

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 {$}?

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 11:50:10 GMT

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

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 11:56:24 GMT

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

[Weight Band] for D1 and

[Zone] for D2

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 12:04:21 GMT

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.

Re: ColorMix in Matrix PivotTable

markus_dreuw — Tue, 03 Apr 2018 12:23:34 GMT

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?!

Re: ColorMix in Matrix PivotTable

rubenmarin — Tue, 03 Apr 2018 13:49:43 GMT

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)