Conditional BackgroundColor, Box Chart

[bmchale1983]

I have color coded my regions(4 different colors) and areas(Shades of these colors) which conditional changes dimensions,

EGroup: Count(DISTINCT(EGroup))>1

Area: Count(DISTINCT(EGroup))=1 and Count(DISTINCT(EArea))>1

For this the following equation worked(if(getselectedcount(EGroup)=1,cAREAColor,cGroupColor) , however the business would like to drill down one more layer and color code employees of region based on targets.  Current I have a gauge with the following equation (count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing)) with segments bounds at .5, .8, and 1.2 for red, amber, green and would like to add this to my conditional background color if statement, but unsure how to fold it in.  Any help would be greatly appreciated.

This is where I'm at, but everything coming out amber right now.

if(getselectedcount(EGroup)>1,cGroupColor,
if(getselectedcount(EGroup)=1 and GetSelectedCount(EArea)>1, cAREAColor, cAmber))


//(count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))

[Anil_Babu_Samineni]

Would you explain little more what are these in your statement? cGroupColor, cAreaColor, Camber

[bmchale1983]

They are color tables ie

EArea	Color
BRONX	RGB(25,65,115)
BROOKLYN	RGB(50,86,130)
NASSAU	RGB(76,107,146)

[Anil_Babu_Samineni]

I assume, you may need $ prompt over colour statement. Will you able to provide sample to work?

[bmchale1983]

I cannot provide same unless I make simple version, but essentially...

Expression 1:

(count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))

Expression 2

if((count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))>=1, 1,
if((count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))>.5, 2,
3))

I have two issues, I need to create a box chart with green when key 1, amber, key 2, and red 3, but its conditionally dimensions and I have working for first 2 with an aligned excel rgb table file, this will need to be based of a calculation.  This is working, but not accouting for R/A/G conditional color I need to add.  the cColor are variable RGB's.

Secondly, I can't get a count of Person by Key in a Text Box, any help on either or both would be awesome; I am stumped.

DIM	Expression1	Expression2
Person	Percent of Target	Key
1	0.6	2
2	1.2	1
3	0.3	1
4	2	3
Count
>11	2
.5-1	1
<.5	1

[bmchale1983]

I made a really simplified sample that you may be able to work with

[bmchale1983]

Well I figured out why it was not working was because there was no selection made for colors, still need to figure out how to total individual counts based on percentage target field....

if(getselectedcount(EGroup)>1 or getselectedcount(EGroup)=0,cGroupColor,
if(getselectedcount(EGroup)=1 and (GetSelectedCount(EArea)>1 or GetSelectedCount(EArea)=0) ,cAREAColor,
if(GetSelectedCount(EArea)=1 and (count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))>1, cDarkGreen,
if(GetSelectedCount(EArea)=1 and (count({<[DateType] = {"Fundings"} >} Loan_Amount)) /(sum(LO_Daily_Targets_Closing))>.5, cAmber,cHSBCCanaryRed))))

[Anil_Babu_Samineni]

Even, If you are not selected at least one colour will come, Right? But in your condition The OR operator works of any one condition that means wither first condition or second condition.