Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
cluscombe
Contributor III
Contributor III

heat map with pivot table

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.

pivot ss.jpg

9 Replies
mikaelsc
Specialist
Specialist

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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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

mikaelsc
Specialist
Specialist

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

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
Contributor III
Contributor III
Author

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

cluscombe
Contributor III
Contributor III
Author

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

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