Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cazztfplease
Creator
Creator

Expression Background Color for Straight Tables

Hi everyone,

I'm fairly sure I am going about this in the hardest way possible, but my logic has me running into walls over and over.  I've found a few threads that have helped me get to where I am currently, but can't seem to get past this point.

I have a straight table that I would like to have up 5 different colors to highlight the background based on various tiers of values.  In the example shown,  I have used an if\else statement to give me two highlights, green and red.  I I know I can use the Visual Cues tab to highlight up to three different ranges.  Ideas on how I can use the expression editor (or other route)  to allow me to highlight 5 ranges of values in different colors? I thought I could structure the statements to allow for (LongEquationValue > 'x' and LongEquationValue <'y') to identify a range for each highlight color, but I may be doing something wrong (clearly) as it doesn't recognize the expression as 'OK'.

For context, each day of the month is a column where the sum of TotalFaults is calculated.  You'll notice a set analysis statement indicating Day ={'1'} for the first of the month, {'2'} for the second, etc.

Thank you for any assistance!

qlikquestion.JPG

qlikquestion2.JPG

1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

Hi Chris,

All you need to do is nest your if statements with all the conditions in the one statement.

eg

if(expression >=0 and CYTD<1000, red(),

     if(expression >=1000 and CYTD <10000, lightred(),

     if(expression >=10000 and CYTD <20000, yellow(),

     if(expression >=20000 and CYTD <50000, blue(),

                    Green()))))

Hope this helps

View solution in original post

15 Replies
Lisa_P
Employee
Employee

Hi Chris,

All you need to do is nest your if statements with all the conditions in the one statement.

eg

if(expression >=0 and CYTD<1000, red(),

     if(expression >=1000 and CYTD <10000, lightred(),

     if(expression >=10000 and CYTD <20000, yellow(),

     if(expression >=20000 and CYTD <50000, blue(),

                    Green()))))

Hope this helps

View solution in original post

cazztfplease
Creator
Creator
Author

Lisa, I tried the nested ifs but had a typo.  Looking at your script I saw my error. Thank you for your response!

cazztfplease
Creator
Creator
Author

Wanna help with this one?  I have the color set to red if the value in the cell is less than 61.5%.  At the top I have an avg % of the values in the column.  Any idea why it is red?

qlikquestion.JPG

Lisa_P
Employee
Employee

Sorry, I can't see the logic here

cazztfplease
Creator
Creator
Author

stalwar1‌? Can you lend me some insight as to why the avg% at the top of the column would be red?  here is the expression:

sunny_talwar

Can you add another expression in this same chart

num((('14.825') - (sum({$<Day = {'1'}>}TotalFaults)))/('14.825'), '##.#')

Make sure to have total mode set to 'Expression Total'

Paste back a screenshot with this information

benazirkhan
Creator II
Creator II

Can you share the app perhaps?

cazztfplease
Creator
Creator
Author

qlikquestion.JPG

-1925.7 is expression total. 83.9% is avg of rows.

sunny_talwar

So, I guess you need to change your expression in the if statement to this

Avg(Aggr((('14.825') - (sum({$<Day = {'1'}>}TotalFaults)))/('14.825'), Month))

and then try it out...

Also, remove num() functions from your if statement as they are not really doing anything within the if statement... (I try to avoid things which don't have any purpose)