topic Re: Expressions - Assigning Background Color by Value in QlikView

Expressions - Assigning Background Color by Value

SClem — Sat, 16 Nov 2024 20:37:44 GMT

I have a table with two columns - Name and Percentage. I am visualizing this information in a pie chart, dimensioned by groupings of Percentage. Here is the formula for my calculated dimension in case that is helpful. I am grouping the values by every 3.5%, offset by 2.5.

=class([Availability (%)],3.5,[Availability (%)],2.5)

I am using a nested IF in my expression to change the background color of the dimension.

if x < 90 --> light red RGB(253,225,219)

else if x < 97 --> light blue RGB(221,233,236)

else (aka x >= 97) --> dark blue RGB(20,72,143)

Here is my formula:

if(Sum([Availability (%)])<90,RGB(253,225,219),if(Sum([Availability (%)])<97,RGB(221,233,236),RGB(20,72,143)))

The issue I'm seeing is that my grouping with values 93.5 <= x < 97 are showing as dark blue, when they should be showing light blue. Can someone help me find my error?

Also since it's probably related, why is there a static value showing in my legend?

Re: Expressions - Assigning Background Color by Value

sunny_talwar — Wed, 19 Jun 2019 18:27:00 GMT

Would you be able to attach you qvw file to check this out?

Re: Expressions - Assigning Background Color by Value

SClem — Wed, 19 Jun 2019 21:11:55 GMT

Thank you, I have attached the qvw file.

Re: Expressions - Assigning Background Color by Value

sunny_talwar — Thu, 20 Jun 2019 10:11:30 GMT

Instead of using Sum() in your color expression, use Avg

If(Avg([Availability (%)]) < 90, RGB(253,225,219), If(Avg([Availability (%)]) < 97, RGB(221,233,236), RGB(20,72,143)))

Re: Expressions - Assigning Background Color by Value

SClem — Thu, 20 Jun 2019 13:40:19 GMT

Thank you this worked! That makes so much sense, and I'm not sure why I was trying to use Sum now that I think about it.

Also, I'm trying to mark your comment as the solution, but it just goes to an error page. 😞 Thank you for your help!

Re: Expressions - Assigning Background Color by Value

sunny_talwar — Thu, 20 Jun 2019 13:53:20 GMT

It is a known issue and community administrators are working on it. Look here for Jeremy's response.

Re: Expressions - Assigning Background Color by Value

sunny_talwar — Thu, 20 Jun 2019 13:57:19 GMT

Seems like it just worked for you now

Re: Expressions - Assigning Background Color by Value

SClem — Thu, 20 Jun 2019 13:59:17 GMT

For some reason it worked to click the "Accept as Solution" link from the email notification, but not on the website.

Re: Expressions - Assigning Background Color by Value

sunny_talwar — Thu, 20 Jun 2019 14:04:11 GMT

Strange, but glad it worked.