Heatmap - Changing the background color based on the rank in that row

[#1]

Hi,

i am trying to craete a kind of heatmap where the maximum value in a row would be highlighted in dark blue (background color of that cell), the second highest should be highlighted in shade lighter then the 1st one, the 3rd higest value should be highlighted in a ligher blue shade coampred to second one and so on.

i am atatching the sample qvd.

I would be greatful if someone can do this for the first 2 rows.

the color codes is as follows:

	r	g	b
color for max value	112	48	160
color for 2nd max	75	172	198
color for 3rd max	219	229	245

Thanks a ton in advance.

[#2]

Thanks a ton...can you just let me know why u used '4,1'......what does that 4 and 1 indicate?

[Nicole-Smith]

This is from the QlikView help:

HRank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other columns containing the current row segment of a pivot table and returns the ranking of the current column within the segment.

This function only works in pivot tables. In all other chart types it returns NULL.

If the pivot table is one-dimensional or if the expression is preceded by the total qualifier, the current row segment is always equal to the entire row. If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order.

The ranking is returned as a dual value, which in the case when each column has a unique ranking will be an integer between 1 and the number of columns in the current row segment.

In the case where several columns share the same ranking, the text and number representation can be controlled as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all columns get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all columns get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all columns in the group.
2	Average rank on all columns in the group.
3	Highest rank on all columns in the group.
4	Lowest rank on first column, then incremented by one for each column in the group.

The third parameter format specifies the text representation of the function result.

format

0 (default)	Low value&' - '&high value on all columns in the group (e.g. 3 - 4).
1	Low value on all columns in the group.
2	Low value on first column, blank on the following columns in the group.

The order of columns for mode 4 and format 2 is determined by the sort order of the chart dimensions.

Examples:

HRank( sum( Sales ))

HRank( sum( Sales ), 2 )

HRank( sum( Sales ), 0, 1 )

I had to use the 4,1 in order to get the rank for each value (instead of just the high or low).