topic Re: heat map with pivot table in App Development

heat map with pivot table

cluscombe — Mon, 04 Jun 2018 19:06:44 GMT

Hey There -

I have a pivot table with dates across the top (Transaction Date), codes (TCDC) down the side, and sum(Sum Amount) as the measure. I would like to apply a heat map to make is easy to identify when sum(Sum Amount) for a specific Transaction Date is unusual as compared to the rest Transaction Dates. I have highlighted two days in the attached screenshot that I want to jump out.

I suspect that colormix1 is the way to go, I just cant seem to figure it out.

Re: heat map with pivot table

mikaelsc — Mon, 04 Jun 2018 19:37:14 GMT

Here's a good starting point: https://community.qlik.com/thread/200871

in your case, you'll have 2 dimensions in the AGGR() function (Transaction Date and TCDC)

look out for performance (with the number of possible dates...)

Re: heat map with pivot table

cluscombe — Mon, 04 Jun 2018 20:28:59 GMT

So if I just wanted to focus on the TCDC and ignore transaction date, would my function look something like this?

Colormix1((sum([Sum Amount])-$(=min(aggr( sum([Sum Amount]), TCDC)) ,blue(),green()))

Re: heat map with pivot table

mikaelsc — Mon, 04 Jun 2018 20:38:30 GMT

nop. you need the second part as well.

Colormix1((sum([Sales])-$(=min(aggr( sum([Sales]), CategoryName))))/$(=(max(aggr( sum([Sales]),CategoryName))

-min(aggr( sum([Sales]), CategoryName)))),white(),$(vSalmon))

as the whole idea is to transform the range of your values to values between 0 and 1 as expected by the colormix1 function (colormix2 is -1 to 1 )

Re: heat map with pivot table

cluscombe — Mon, 04 Jun 2018 22:11:00 GMT

hmm I do have positive and negative values in my dataset, does that mean I should be using colormix2?

Re: heat map with pivot table

mikaelsc — Mon, 04 Jun 2018 22:25:23 GMT

or download a qlikview version to use the colormix wizzard... (maybe one of the only things I'd still open a qlikview for) and copy paste results in QS 🙂

Using ColorMix In Qlikview

Re: heat map with pivot table

brunobertels — Tue, 05 Jun 2018 11:13:21 GMT

As colormix2 range the value between -1 an 0 , it accept 3 colors définitions in this order

Colormix2 (Value ,ColorMinusOne , ColorOne[ , ColorZero])

if you have negative values it may be sweetable to use colormix 2 rather than colormix1

Re: heat map with pivot table

cluscombe — Tue, 05 Jun 2018 17:14:35 GMT

Yikes - what came out of qlikview seems even more confusing and I didnt see a place in the wizard to add TCDC:

ColorMix1 ((1+Sign(2*(Sum ([$ Amount])-RangeMin (top(total Sum ([$ Amount]),1,NoOfRows(total))))/(RangeMax (top(total Sum ([$ Amount]),1,NoOfRows(total)))-RangeMin (top(total Sum ([$ Amount]),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(Sum ([$ Amount])-RangeMin (top(total Sum ([$ Amount]),1,NoOfRows(total))))/(RangeMax (top(total Sum ([$ Amount]),1,NoOfRows(total)))-RangeMin (top(total Sum ([$ Amount]),1,NoOfRows(total))))-1))))/2, ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0))

Re: heat map with pivot table

vvira1316 — Tue, 05 Jun 2018 17:32:26 GMT

Hi Chris,

Following may help.

color code

Text Color Expression of a pivot with Coloring logics

Relative Text Color expression

Re: heat map with pivot table

cluscombe — Wed, 06 Jun 2018 16:38:12 GMT

Was never able to get colormix to work, but found this extension that does exactly what I wanted::

http://branch.qlik.com/#!/project/56728f52d1e497241ae6983e