Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mishraamit2485
Contributor

Heatmap Expression in pivot table

I have to create a heat map dashboard the requirement is something like.

if Col A/Col B is <= +/- 3% show same color (lets say Light Red) for -ve 3 % as well as for 3 %+ve

Col A/Col B is > +/-3  and <=+/-5% show same Color (Lets say  Red )

ColA/ColB is > +/-5  and<=+/-10% show the same color (Dark Red)

ColA/ColB is > +/-10  and <=+/-20% show the same color (Darkest Red)

my problem is when i'm writing the expression in pivot under background color it is failing.

It is always in the first line of code never going to else part.


attaching the app

1 Solution

Accepted Solutions
qlikmsg4u
Valued Contributor

Re: Heatmap Expression in pivot table

Try this expression and change the colors as per your requirement

=if( Fabs((ColA/ColB)-1) >=0.03 and Fabs((ColA/ColB)-1)<=0.05,RGB(206,103,0),

  If( Fabs((ColA/ColB)-1) >= 0.05 and Fabs((ColA/ColB)-1) <= 0.1,RGB(176,88,0),

  If( Fabs((ColA/ColB)-1) >= 0.1 and Fabs((ColA/ColB)-1) <= 0.5, RGB(138,69,0),

  If( Fabs((ColA/ColB)-1) >= 0.5 and Fabs((ColA/ColB)-1) <= 1, RGB(255,255,0),RGB(238,69,0)

))))

7 Replies
qlikmsg4u
Valued Contributor

Re: Heatmap Expression in pivot table

Hi Amit,

See the attached app, I used Colormix2 function instead  of If conditions. Let us know if it is useful for you or not

Not applicable

Re: Heatmap Expression in pivot table

Hi Amit,

Your expression is not having any else condition, except top two values remaining values are not satisfying the condition so default color(white) is assigned for them.

Try giving an else color condition like below --

if(((((ColA/ColB)-1)>=.3)  or (((ColA/ColB)-1)<=-.3)),RGB(206,103,0),

  if(((((ColA/ColB)-1)>=.5)  or (((ColA/ColB)-1)<=-.5)),RGB(204,102,0),

  if(((((ColA/ColB)-1)>=1)  or (((ColA/ColB)-1)<=-1)),RGB(176,88,0),

  if(((((ColA/ColB)-1)>=1.5)  or (((ColA/ColB)-1)<=-1.5)),RGB(138,69,0),

  if(((((ColA/ColB)-1)>=2) or (((ColA/ColB)-1)<=-2)),RGB(255,255,0),

  RGB(255,0,0)

)))))

You will see the other values in bright red color.

Condition is working fine, try to give more if conditions to satisfy all the possible values.

Hope this helps you.

P.S. I have removed single quotes for the values ('.3' to .3) as they are values but not strings

Thanks,

Harika

mishraamit2485
Contributor

Re: Heatmap Expression in pivot table

Hi Harika,

My requirement is like for some range it should give me one color and for some other col, suppose the value is (Cola/Colb -1 )is 1 in that case it should go to line three but it will show me the color of line 1 only..correct me if i'm wrong.

mishraamit2485
Contributor

Re: Heatmap Expression in pivot table

Hi KN Thanks a lot

looks fine just one thing is missing is for +ve 50% or -ve 50% it should show the same color so i tried using fabs function there it works well.

can we show the range of colors, like for 3 to 5 % show one color and for 5 to 10 show other for 10-20 show other?

Updated the first app with new one(not able to attach the app while replying)

Two questions.

1) when I'm using the color mix wizard a big expression is coming up where as ur exp is quite simple what would be the diff between them(Wizard VS urs EXP)

2) is it recommenced to use color mix wizard or is there any other way, like I can just create the colors in inline and by using apply map i can get the colors with that i think maintenance will be quite easy(Colors in charts) !

mishraamit2485
Contributor

Re: Heatmap Expression in pivot table

One problem with that we can only define the Max and Min range, only those colors will come which are under that range...if a user wants to give some custom color like for a specific range user wants a little more darker shade that can not be done i guess. we have to accept colors from the range only.

Highlighted
qlikmsg4u
Valued Contributor

Re: Heatmap Expression in pivot table

Yes, if you want to define custom colours use If conditions

qlikmsg4u
Valued Contributor

Re: Heatmap Expression in pivot table

Try this expression and change the colors as per your requirement

=if( Fabs((ColA/ColB)-1) >=0.03 and Fabs((ColA/ColB)-1)<=0.05,RGB(206,103,0),

  If( Fabs((ColA/ColB)-1) >= 0.05 and Fabs((ColA/ColB)-1) <= 0.1,RGB(176,88,0),

  If( Fabs((ColA/ColB)-1) >= 0.1 and Fabs((ColA/ColB)-1) <= 0.5, RGB(138,69,0),

  If( Fabs((ColA/ColB)-1) >= 0.5 and Fabs((ColA/ColB)-1) <= 1, RGB(255,255,0),RGB(238,69,0)

))))