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

[#1]

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

E

[Nicole-Smith]

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

[#3]

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.

[Nicole-Smith · Accepted solution]

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?

[#5]

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

It is working!

Thanks very much

Ewa

[#6]

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

[Nicole-Smith]

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

[#8]

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