topic Re: 10% lower/higher - Text colour or Background Colour in Expression in QlikView

10% lower/higher - Text colour or Background Colour in Expression

Mon, 04 Apr 2016 17:37:15 GMT

Hello

I am trying to colour text or background in the chart/table by using "Text colour" or "Background colour" in the expression.

I would like to write a formula to say:

if (CALCULATION) is 10% lower than (CALCULATION2), then it should be red. if (CALCULATION) is 10% higher than (CALCULATION2), then it should be green.

Any ideas how to do this?

Thanks

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Mon, 04 Apr 2016 18:33:28 GMT

In your Background Color Expression, you would write something like the following:

if(sum(Sales1)<=.9*sum(Sales2), rgb(255,0,0),

if(sum(Sales1)*.9>=sum(Sales2), rgb(0,255,0)))

Re: 10% lower/higher - Text colour or Background Colour in Expression

satishkurra — Mon, 04 Apr 2016 18:51:56 GMT

Just an add on, if you are using straight table or pivot table, we can also use Visual Cues option.

Re: 10% lower/higher - Text colour or Background Colour in Expression

Mon, 04 Apr 2016 20:49:28 GMT

Thanks Nicole for your reply...Shouldn't it be slightly different (the second part of the formula)?:

if(sum(Sales1)<=.9*sum(Sales2), rgb(255,0,0),

if(sum(Sales1)>=.9*sum(Sales2), rgb(0,255,0)))

I'm trying the above but it doesn't seem to work (Error in Expression), perhaps something to do with typing ".9"? Or perhaps it somewhere misses a bracket or a comma?

The expressions work fine with other charts, so I don;t think it's an issue with expressions, but more of comma/bracket issue. I would be grateful for looking at the below.

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE]))<=.9*sum({1 <[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'}>}[FPE])), rgb(255,0,0),

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE]))>=.9*sum({1 <[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'}>}[FPE])), rgb(0,255,0)))

Unfortunately Visual Cues won't work as I would like this to work with particular expression (and I have a few expressions in the chart).

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Mon, 04 Apr 2016 21:23:27 GMT

Ewa Wszelaka wrote:

Thanks Nicole for your reply...Shouldn't it be slightly different (the second part of the formula)?:

if(sum(Sales1)<=.9*sum(Sales2), rgb(255,0,0),

if(sum(Sales1)>=.9*sum(Sales2), rgb(0,255,0)))

I think the way that I originally had it is the way you want it.

For example, if sum(Sales1) = 80 and sum(Sales2) = 100, you would want to color sum(Sales1) as red because it is 10% lower: 80 <= 0.9*100 = true, which would result in red.

However, if sum(Sales1) = 100 and sum(Sales2) = 90, you would want to color sum(Sales1) as green because it is 10% higher: 100*0.9 >= 90 = true, which would result in green.

If you were to do it your way, it would be backwards, i.e., if sum(Sales1) = 90 and sum(Sales2) = 100, you would want to color sum(Sales1) as red because it is 10% lower, but you would actually color it green because of your calculation: 90 >= 100*0.9 = true, which would result in green.

Ewa Wszelaka wrote:

I'm trying the above but it doesn't seem to work (Error in Expression), perhaps something to do with typing ".9"? Or perhaps it somewhere misses a bracket or a comma?

The expressions work fine with other charts, so I don;t think it's an issue with expressions, but more of comma/bracket issue. I would be grateful for looking at the below.

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE]))<=.9*sum({1 <[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'}>}[FPE])), rgb(255,0,0),

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE]))>=.9*sum({1 <[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'}>}[FPE])), rgb(0,255,0)))

Typing ".9" should not result in an error. You have 16 closing parentheses but only 12 opening ones, so that's probably the issue. The following should fix it:

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE])*.9>=sum({1 <[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'}>}[FPE]), rgb(0,255,0)))

Re: 10% lower/higher - Text colour or Background Colour in Expression

Tue, 05 Apr 2016 07:47:52 GMT

Nicole

You are amazing! The formula seems to work!

I'm still struggling to understand the second part of the formula. I can tell why my suggestion would not work.

However, I am not convinced wither about the suggested second part of the formula.

Because eventually I would like to calculate 10% higher and 10% lower than Sales 2 and compare this to Sales 1 I think it should be in fact:

if(sum(Sales1)<=.9*sum(Sales2), rgb(255,0,0),

if(sum(Sales1)>=1.1*sum(Sales2), rgb(0,255,0)))

In this way I can calculate 10% lower than Sales2(.9*Sales2) and 10% higher than Sales2 (1.1*Sales2) and compare it to Sales 1 accordingly.

If I multiple .9*Sales1, I don't think I get what I need.

Thanks

Ewa

Re: 10% lower/higher - Text colour or Background Colour in Expression

Tue, 05 Apr 2016 15:19:21 GMT

I am trying the below, but all fields become lime green including those below 10% . Is there anything else missing or is it in fact the calculation? I tried your original suggestion but it didn't seem to work.

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE])<=.9*sum({1 <[Classification]={'P'}, [latest]={'latest'},[Outcome]={'COMPLETE'},[Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'},[Classification]={'1','2A','2B','3','P'},[latest]={'latest'},[Outcome]={'COMPLETE'}>}[FPE]), rgb(255,0,0), if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE])>=1.1*sum({1 <[Classification]={'P'}, [latest]={'latest'},[Outcome]={'COMPLETE'},[Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({1<[Entry Cohort]={'2010','2011','2012'},[Classification]={'1','2A','2B','3','P'},[latest]={'latest'},[Outcome]={'COMPLETE'}>}[FPE]), rgb(0,255,0)))

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Tue, 05 Apr 2016 16:23:27 GMT

sum(Sales1)*.9>=sum(Sales2) and sum(Sales1)>=1.1*sum(Sales2) are equivalent, so using either would work. If it's easier for you to understand the latter, then just use that one.

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Tue, 05 Apr 2016 16:51:47 GMT

Can you post a sample file?

Preparing examples for Upload - Reduction and Data Scrambling

Re: 10% lower/higher - Text colour or Background Colour in Expression

Tue, 05 Apr 2016 18:12:36 GMT

Thanks Nicole. See attached. I hope that's clear,

When you get to the Expression for "1" in the chart properties and Background colour you will see the discussed formula.

The tab has got some actions assigned (Select in Field) that I wouldn't remove, as I need them (Entry cohort/latest/lates/outcome/classification). I left the Current Selection box so that you are aware of this.

Hence in the formula for background colour, my set analysis for the college (which is Sales2) has got all the conditions... I hope this makes sense.

I can't wait to find out whether there is a way around it! It would be so useful!

Thanks

Ewa

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Tue, 05 Apr 2016 18:24:44 GMT

According to what you're calculating, they should all be green.

I took the two expressions that you're comparing and adding them to separate columns. In every case, Comp1 is >10% higher than Comp2, hence why they're all green.

Disability general	Entry Cohort	1	Comp1	Comp2
		23%	0.2255	0.0093
Disability	2010	16%	0.1591	0.0221
Disability	2011	22%	0.2205	0.0163
Disability	2012	16%	0.1603	0.0068
No disability	2010	23%	0.2292	0.0061
No disability	2011	24%	0.2415	0.0069
No disability	2012	22%	0.2211	0.0140

Re: 10% lower/higher - Text colour or Background Colour in Expression

Tue, 05 Apr 2016 18:46:50 GMT

I assumeComp1 is College level.

This means that the second part of my expression might not be sinking in correctly, as the figures should be different...

The column "1" has got correct values for "Science".

As I am trying to compare Comp1 (College) to e.g. Science, I am trying to create an expression for the whole college (so Science+Arts+Management+External) for the same conditions that are used in the tab as Actions "Select in Field".

So for No disability students the College level would be:

Row Labels	2010	2011	2012
1	20.0%	22.4%	22.0%

And for disability students it would be:

Row Labels	2010	2011	2012
1	18.2%	21.2%	15.6%

For Disability 2010, Science would be red.

Any ideas why this is the case? It might have something to do with the set analysis I guess, or a number of conditions I've got?

Thanks a lot for your time and help.

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Tue, 05 Apr 2016 19:24:58 GMT

I think this might be what you want:

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE])<=.9*sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'},Faculty=>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'},Faculty=>}[FPE]), rgb(255,0,0),

if(sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'}>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'}>}[FPE])>=1.1*sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'},Faculty=>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'},Faculty=>}[FPE]), rgb(0,255,0)))

The "Sales2" part of the expression was changed to:

sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'},Faculty=>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'},Faculty=>}[FPE])

I used the same expression that you're using to calculate the "Science" portion but ignored any selections in the "Science" field ("Faculty").

You had the 1 in there which was ignoring selections in everything, but maybe that's not what you actually wanted to do?

Re: 10% lower/higher - Text colour or Background Colour in Expression

Wed, 06 Apr 2016 07:36:03 GMT

Nicole, you are a star! I cannot express how much I appreciate your help.

It is working!

Thanks very much

Ewa

Re: 10% lower/higher - Text colour or Background Colour in Expression

Wed, 06 Apr 2016 10:38:27 GMT

Nicole, thanks again for your help.

I've just realised that the formula only works if I select a faculty, but it doesn't work when I select a department or a programme).

Is there any way of creating a formula where the second part of the formula (Sales2) always calculates the College level (which is all faculties) and interacts with Faculty, Department and Programme level?

Thanks

Re: 10% lower/higher - Text colour or Background Colour in Expression

Nicole-Smith — Wed, 06 Apr 2016 14:35:15 GMT

So you also want to ignore selections in Department and Programme? You would just add them in like we did Faculty as fields to ignore selections in:

sum({<[Classification]={"1"}, [Entry Cohort]={'2010','2011','2012'},Faculty=,Department=,Programme=>} [FPE])/ SUM ({<[Entry Cohort]={'2010','2011','2012'},Faculty=,Department=,Programme=>}[FPE])

Re: 10% lower/higher - Text colour or Background Colour in Expression

Wed, 06 Apr 2016 15:19:51 GMT

Nicole, I think this is it, as when I select Department and Programme, the colouring seems to be working!

Sorry for all my basic questions, I am quite new to Qlikview.

I am very grateful for all your help.

Best

Ewa