topic Re: Expression Background Color for Straight Tables in QlikView

Expression Background Color for Straight Tables

cazztfplease — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Expression Background Color for Straight Tables

Lisa_P — Mon, 07 May 2018 23:43:01 GMT

Hi Chris,

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

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

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 05:40:25 GMT

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

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 05:42:54 GMT

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?

Re: Expression Background Color for Straight Tables

Lisa_P — Tue, 08 May 2018 05:55:03 GMT

Sorry, I can't see the logic here

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 12:41:08 GMT

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:

Re: Expression Background Color for Straight Tables

sunny_talwar — Tue, 08 May 2018 12:48:25 GMT

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

Re: Expression Background Color for Straight Tables

Anonymous — Tue, 08 May 2018 13:06:39 GMT

Can you share the app perhaps?

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 14:21:19 GMT

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

Re: Expression Background Color for Straight Tables

sunny_talwar — Tue, 08 May 2018 14:25:26 GMT

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)

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 14:37:50 GMT

Thanks Sunny! Yeah, the num stuff just never got cleaned up from when I was attempting to structure it in the cell a certain way, but I tend to agree with that sentiment.

Re: Expression Background Color for Straight Tables

sunny_talwar — Tue, 08 May 2018 14:39:43 GMT

So, did adding Avg(Aggr()) resolved the issue?

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 14:56:57 GMT

I copy/pasted your expression and realized you had 14.825, not 14,825, which was giving me weird numbers I was trying to figure out. (Your expression is the expression I used in in column '1'.)

Now that I see why the numbers were off, I fixed the expression so the numbers look right. I applied the avg(aggr()) function to column '1', but it did not appear to effect the colors.

This is the cleaned up expression for column '1' with the background color nested if statement applied(below that):

;

Re: Expression Background Color for Straight Tables

sunny_talwar — Tue, 08 May 2018 15:02:58 GMT

You did not apply the new expression for the color expression?

You need to apply the Avg(Aggr()) in the above expression

Anywhere you see this

(('14,825') - (sum({$<Day = {'1'}>}TotalFaults)))/('14,825')

Replace it with

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

Re: Expression Background Color for Straight Tables

cazztfplease — Tue, 08 May 2018 15:31:07 GMT

Sunny,

That worked! Can you explain WHY?!?!?

Re: Expression Background Color for Straight Tables

sunny_talwar — Tue, 08 May 2018 15:32:26 GMT

Avg(Aggr()) fixed the total value from -1925.7 to 0.83944... this fixed the comparison and hence the color