topic Re: Heat Map Table in QlikView

Heat Map Table

evansabres — Wed, 21 Feb 2018 16:05:41 GMT

I have a field called 'mjrGRP'

There are values in this field of 'Food' 'Beer' 'Liquour' 'Wine'

The metric for this field is another field called 'salesTTL'

Sample data:

mjrGRP salesTTL

Food 12.00

Food 20.00

Beer 8.00

Liquor 10.00

Wine 9.00

I want to create a table that has an equation for each mjrGRP, I already have the expression(s):

=NUM(Sum({<majorGRP = {'Food'}>}salesTTL), '$##,###.##')

What I want to do is create a table that has the salesTTL for each mjrGRP in four separate columns.

So My table would look something like below

Food Beer Liquor Wine

Sales by Group $50,000 $45,000 $30,000 $10,000

Then, ideally, I would like to make this look like a heat map

Re: Heat Map Table

sunny_talwar — Wed, 21 Feb 2018 16:17:30 GMT

May be create a pivot table where mjrGRP is the pivoted dimension?

Re: Heat Map Table

evansabres — Wed, 21 Feb 2018 16:19:51 GMT

I was able to use a straight table by having the dimension =If(not Match(majorGRP, 'Food', 'Beer', 'Liquor', 'Wine', 'Retail'), majorGRP) and then the expressions as above. Still looking to accomplish the heat map

Re: Heat Map Table

sunny_talwar — Wed, 21 Feb 2018 16:21:47 GMT

Why don't you try with the pivot table?

Re: Heat Map Table

evansabres — Wed, 21 Feb 2018 16:23:15 GMT

Will that allow for the heat map? I have a working solution for the first question

Re: Heat Map Table

marcus_sommer — Wed, 21 Feb 2018 16:24:47 GMT

Maybe this is helpful: The great heat chart.

- Marcus

Re: Heat Map Table

sunny_talwar — Wed, 21 Feb 2018 16:26:06 GMT

It should.... marcus_sommer‌ shared a link where a pivot table has been used

Re: Heat Map Table

evansabres — Wed, 21 Feb 2018 16:36:44 GMT

For my data, the straight table is working better.

Is it possible to have the background of each cell in my table show a color in relation to the total.

My equation for a particular majorGRP is =NUM(Sum({<majorGRP = {'Food'}>}salesTTL), '$##,###.##')

So I am thinking that divided by NUM(SUM(salesTTL) to show the percentage of sales?

Re: Heat Map Table

marcus_sommer — Wed, 21 Feb 2018 16:54:04 GMT

I think it should be possible also with a straight-table and but suggest that you start to understand the logic with the example-pivot and then adapting it to your goals.

- Marcus