Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
SClem
New Contributor II

Expressions - Assigning Background Color by Value

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?

qlikview-screenshot.pngqlikview-screenshot2.png

2 Solutions

Accepted Solutions

Re: Expressions - Assigning Background Color by Value

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

image.png 

Re: Expressions - Assigning Background Color by Value

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

8 Replies

Re: Expressions - Assigning Background Color by Value

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

SClem
New Contributor II

Re: Expressions - Assigning Background Color by Value

Thank you, I have attached the qvw file.

Re: Expressions - Assigning Background Color by Value

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

image.png 

SClem
New Contributor II

Re: Expressions - Assigning Background Color by Value

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

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

Seems like it just worked for you now

SClem
New Contributor II

Re: Expressions - Assigning Background Color by Value

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

Strange, but glad it worked.