Conditional Expression Functions for Color expression?

[ishansjain2095]

Hi All,

Facing a major issue in qlik sense dual(if's) function like wanted Funds% to be compared as Target as color map Funds% as ranges such as:

>=0 and <0.01 should be green,

>0.01 and <0.2 should be yellow,

>0.2 should be rbg(255,125,0)

and at last >0.5 should be red;

as shown in excel sheet col(Funds%)

Note: I have used Funds% as "AJS/Act"

            And Target(AON*Funds BNK) as "AON*Funds BNK" where AON is shown as month days i.e. 30 days.

You can see the color format in excel from color formatting tab and see the functions applied.

Required debugging in below expression:

if(
Funds %
>=
0
and
Funds %
<
(Target(AON*Funds BNK)),Green(),
//------2------//
if(
Funds %
>
(Target(AON*Funds BNK))
and
Funds %
<
(Target(AON*Funds BNK))
+
(Target(AON*Funds BNK), Yellow(),
//------3------//
if(
Funds %
<
(Target(AON*Funds BNK))
+
(Target(AON*Funds BNK)),RGB(255,125,0),
//------4------//
if(
Funds %
>
(Target(AON*Funds BNK))
+
(Target(AON*Funds BNK)),Red()))))

Please check this expression with excel file attached in qlik sense app.

Regards,

Ishan

[Lauri]

Field names with spaces need to be enclosed in square brackets or double quotes.

[ishansjain2095]

Can you write the script for better understanding!!!

Regards,

Ishan

[Lauri]

A field name like Funds % needs to be written as [Funds %] or "Funds %"

[ishansjain2095]

Hi Lauri,

I worked in it color formatting doesn't matches as excel sheet "conditional formatting". Can you help with the conditions that which condition can be used color formatting ranges shown below :

i) Funds% <=0

ii) Funds% >.01 

i) & ii) should be "Green"

iii) Funds% >= .01

iv) Funds% <= .2

iii) & iv) should be "Yellow"

v) Funds% should be Between .2 and .5 and display as Ember "RGB(255,125,0)"

vi) Funds% > .5 should display as "Red"

Which condition should I apply Dual(if's) condition isn't working for me.

Please suggest!!!!.

Regards,

Ishan

[Lauri]

I am guessing you mean: if Funds% between 0 and 0.01 then green, etc. Your < and > symbols were reversed.

If([Funds%]>=0 and [Funds%]<0.01, green(), If([Funds%]>=0.01 and [Funds%]<0.2, yellow(), If([Funds%]>=0.2 and [Funds%]<=0.5, RGB(255,125,0), red())))