Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Creator III
Creator III

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

Contributor III
Contributor III

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

Creator III
Creator III

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 )

Contributor III
Contributor III

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

Creator III
Creator III

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

Specialist II
Specialist II

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

Contributor III
Contributor III

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

Specialist II
Specialist II

Contributor III
Contributor III

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

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