heat map with pivot table

[cluscombe]

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.

[mikaelsc]

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

[cluscombe]

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

[mikaelsc]

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 )

[cluscombe]

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

[mikaelsc]

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

[brunobertels]

Hi

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

[cluscombe]

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

[vvira1316]

Hi Chris,

Following may help.

color code

Text Color Expression of a pivot with Coloring logics

Relative Text Color expression

[cluscombe]

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

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