Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
Sunny, you rock man.
You have helped me many times already.
Thank you!
I am once again glad that I am able to 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.
Nvm... Got it
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.