Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patriciousa
Creator II
Creator II

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
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

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
Creator II
Creator II
Author

Sunny, you rock man.

You have helped me many times already.

Thank you!

sunny_talwar

I am once again glad that I am able to help

patriciousa
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Nvm... Got it

swuehl
MVP
MVP

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.