Pivot table like a heatmap

[sophie_bd]

Hello,

i have a pivot with 2 dimensions Country and ValueList('Famille','Sous-Famille', 'Segment')

with one expression : 

=
IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Famille', [_AMOUNT (CC3 Famille NR)],
IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Sous-Famille', [_AMOUNT (CC3 SS Famille NR)],
IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Segment', [_AMOUNT (CC3 Segment NR)],
)))
/1000000

i want to colorize the pivot table like a heatmap so i use this expression : ColorMix1( rank(total column(1))/NoOfRows(TOTAL) , rgb(222,84,4), rgb(255,255,255))

but it doesn't work :

do you have an idea for this problem ,

Thanks,

Sophie

[theoat]

First, calculate 2 variables that will calculate the maximum and minimum of your table (let's imagine with 3 measurements).
vTableMax:
$(=rangemax(max(aggr([Measure1],Country)),max(aggr([Measure2],Country)),max(aggr([Measure3],Country))))
vTableMin
$(=rangemin(min(alt(aggr([Nb méd or],DelegationFr),0)),min(alt(aggr([Nb méd argent],DelegationFr)),0),min(alt(aggr([Nb méd bronze],DelegationFr)),0)))
Next, go to the background function of each measure, and add this (example for the first measure):
=ColorMix1(([Measure1]-$(MinTable))/($(MaxTable)-$(MinTable)),RGB(255,23,23),RGB(102,104,0))

Now you just have to adapt to your case, this solution is functional on my side.


Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[sophie_bd]

Hello,

thanks for the answer, but i have only one mesure :

IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Famille', [_AMOUNT (CC3 Famille NR)],
IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Sous-Famille', [_AMOUNT (CC3 SS Famille NR)],
IF( ValueList('Famille','Sous-Famille', 'Segment') = 'Segment', [_AMOUNT (CC3 Segment NR)],
)))

Sophie

[theoat · Accepted solution]

So, in this case, you have just to do this :
vTableMax:
$(=max(aggr([Measure],Country)))
vTableMin
$(=min(alt(aggr([Measure],Country),0)))
Next, go to the background function of each column, add this:
=ColorMix1(([Measure]-$(MinTable))/($(MaxTable)-$(MinTable)),RGB(255,23,23),RGB(102,104,0))

[stevedark · Accepted solution]

Hi both,

I think that in order to work across the different values in the value list, that would also need to be added into the aggr statement. I don't think the aggr would be happy with a valuelist (but I've not tried it), but to make things simpler I would have the value list as a data island, created in the load script.

Simply add this to the load script:

CC3:
LOAD
  CC3
INLINE [
CC3
Famille
Sous-Famille
Segment
];

This will then give you a field in the data model you can add as a dimension to the pivot. Your expression then gets a whole lot tidier:

=IF(CC3 = 'Famille', [_AMOUNT (CC3 Famille NR)],
IF(CC3 = 'Sous-Famille', [_AMOUNT (CC3 SS Famille NR)],
IF(CC3 = 'Segment', [_AMOUNT (CC3 Segment NR)],
))) /1000000

Then the two variables that @theoat suggests you create would have the dimension referenced there also:

$(=max(aggr([Measure],Country,CC3)))

Hope that helps.

Steve

[sophie_bd]

Hello both,

i use the two solution, Dimension in the script (AGGR is not aware with value list) and the two variables for colormix and it works.

So thanks both