topic Re: Conditional Expression Functions for Color expression? in App Development

Conditional Expression Functions for Color expression?

ishansjain2095 — Mon, 20 Dec 2021 21:32:42 GMT

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

Re: Conditional Expression Functions for Color expression?

Lauri — Mon, 02 Mar 2020 15:36:06 GMT

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

Re: Conditional Expression Functions for Color expression?

ishansjain2095 — Tue, 03 Mar 2020 05:26:04 GMT

Can you write the script for better understanding!!!

Regards,

Ishan

Re: Conditional Expression Functions for Color expression?

Lauri — Tue, 03 Mar 2020 14:29:27 GMT

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

Re: Conditional Expression Functions for Color expression?

ishansjain2095 — Wed, 04 Mar 2020 09:24:23 GMT

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

Re: Conditional Expression Functions for Color expression?

Lauri — Wed, 04 Mar 2020 13:37:37 GMT

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