Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

patriciousa
Contributor

Multiple IF and Sum syntax - Color Background Help

Hi Community.

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

I have a table like this in Qlik Sense.

NamesCategory%
PatrickB45
MathewA80
LeoJunior95

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Multiple IF and Sum syntax - Color Background Help

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

6 Replies
MVP
MVP

Re: Multiple IF and Sum syntax - Color Background Help

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

patriciousa
Contributor

Re: Multiple IF and Sum syntax - Color Background Help

Sunny, you rock man.

You have helped me many times already.

Thank you!

MVP
MVP

Re: Multiple IF and Sum syntax - Color Background Help

I am once again glad that I am able to help

patriciousa
Contributor

Re: Multiple IF and Sum syntax - Color Background Help

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.

patriciousa
Contributor

Re: Multiple IF and Sum syntax - Color Background Help

Nvm... Got it

MVP
MVP

Re: Multiple IF and Sum syntax - Color Background Help

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.