topic Re: Heatmap Expression in pivot table in QlikView

Heatmap Expression in pivot table

mishraamit2485 — Tue, 04 Aug 2015 03:18:51 GMT

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

Re: Heatmap Expression in pivot table

qlikmsg4u — Tue, 04 Aug 2015 05:32:07 GMT

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

Re: Heatmap Expression in pivot table

Tue, 04 Aug 2015 05:34:07 GMT

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

Re: Heatmap Expression in pivot table

mishraamit2485 — Tue, 04 Aug 2015 05:55:23 GMT

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.

Re: Heatmap Expression in pivot table

mishraamit2485 — Tue, 04 Aug 2015 06:09:53 GMT

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

Re: Heatmap Expression in pivot table

mishraamit2485 — Tue, 04 Aug 2015 06:46:20 GMT

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.

Re: Heatmap Expression in pivot table

qlikmsg4u — Tue, 04 Aug 2015 06:48:09 GMT

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

Re: Heatmap Expression in pivot table

qlikmsg4u — Tue, 04 Aug 2015 07:02:42 GMT

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)

))))