Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
))))
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
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
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.
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) !
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.
Yes, if you want to define custom colours use If conditions
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)
))))