topic Re: Multiple IF and Sum syntax - Color Background Help in App Development

Multiple IF and Sum syntax - Color Background Help

patriciousa — Wed, 23 Mar 2016 14:30:34 GMT

Hi Community.

Can anyone help me understand why my code does not work?

I have a table like this in Qlik Sense.

Names	Category	%
Patrick	B	45
Mathew	A	80
Leo	Junior	95

I need to make a color background in the % column.

When Category A is over 75%, paint it in green, red.

When Category B is over 75%, paint it in green, red.

When Category Junio is over 95%. green, if not, red.

The % column has the following sum:

Sum({<"Facturable Real"={Facturable}>} Cantidad) / sum(Cantidad)

So I tried the following syntax:

if(Sum({<"Facturable Real"={Facturable},"Category"={"A"}>} Cantidad)

/Sum({<"Category"={"A"}>} Cantidad)>=0.75,

RGB(50,149,50), RGB(170,57,57) )

So far this one works, but when I try to add more "IF" it gives me a syntax error... I tried using "and" and "or".

.::::::: If i Manage to connect the following it should work... .:::::...:

if(Sum({<"Facturable Real"={Facturable},"Category"={"A"}>} Cantidad)

/Sum({<"Category"={"A"}>} Cantidad)>=0.75,

RGB(50,149,50), RGB(170,57,57) )

if(Sum({<"Facturable Real"={Facturable},"Category"={"B"}>} Cantidad)

/Sum({<"Category"={"B"}>} Cantidad)>=0.75,

RGB(50,149,50), RGB(170,57,57) )

if(Sum({<"Facturable Real"={Facturable},"Category"={"Junior"}>} Cantidad)

/Sum({<"Category"={"Junior"}>} Cantidad)>=0.95,

RGB(50,149,50), RGB(170,57,57) )

Thank you in advance.

Regards.

Re: Multiple IF and Sum syntax - Color Background Help

sunny_talwar — Wed, 23 Mar 2016 14:34:55 GMT

What about this:

If(Sum({<"Facturable Real"={Facturable},"Category"={'A', 'B'}>} Cantidad)/Sum({<"Category"={'A', 'B'}>} Cantidad)>=0.75 or Sum({<"Facturable Real"={Facturable},"Category"={'Junior'}>} Cantidad)/Sum({<"Category"={'Junior'}>} Cantidad)>=0.95, RGB(50,149,50), RGB(170,57,57))

Re: Multiple IF and Sum syntax - Color Background Help

patriciousa — Wed, 23 Mar 2016 14:40:18 GMT

Sunny, you rock man.

You have helped me many times already.

Thank you!

Re: Multiple IF and Sum syntax - Color Background Help

sunny_talwar — Wed, 23 Mar 2016 14:41:02 GMT

I am once again glad that I am able to help

Re: Multiple IF and Sum syntax - Color Background Help

patriciousa — Wed, 23 Mar 2016 14:43:00 GMT

One more quick question. If i want to add a new Category, like, Director and it should be 50%, how? I'm trying to copy your syntax but is giving me syntax error....

Haha Thank you again.

Re: Multiple IF and Sum syntax - Color Background Help

patriciousa — Wed, 23 Mar 2016 14:43:32 GMT

Nvm... Got it

Re: Multiple IF and Sum syntax - Color Background Help

swuehl — Wed, 23 Mar 2016 14:51:34 GMT

Another possible solution could be

If(

Sum({<"Facturable Real"={Facturable}>} Cantidad) / sum(Cantidad)

> Pick(Match(Category,'A','B','Junior'),0.75,0.75,0.95),

RGB(50,149,50), RGB(170,57,57)

)

avoiding multiple aggregations with set expressions.