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]

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

[satishkurra]

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

[#4]

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

[Nicole-Smith]

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

[#6]

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

[#7]

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

[Nicole-Smith]

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.

[Nicole-Smith]

Can you post a sample file?

Preparing examples for Upload - Reduction and Data Scrambling

[#10]

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