6 Replies Latest reply: May 7, 2014 11:43 AM by Michael Reagan

# Expression Attribute Background Color problem

Hello All-

I have a stacked bar chart that represents an Opportunity Pipeline. The sections of the bars are sales stages for the opportunities and the dimension is year-quarter.

The expression is fairly simple:

Sum ({<Opportunities.StageGroup = {'Pending','Committed'}

,FiscalYearName =

,Calendar.RelativeFiscalQuarter = {

'Current Quarter',

'Current Quarter + 1',

'Current Quarter + 2',

'Current Quarter + 3',

'Current Quarter + 4'}

>} Opportunities.Amount)

In the Expression Attribute Background Color, I have the following expression:

=If(Opportunities.StageNumber = '5', \$(vStg5Color),

If(Opportunities.StageNumber = '4B', \$(vStg4BColor),

If(Opportunities.StageNumber = '4A', \$(vStg4AColor),

If(Opportunities.StageNumber = '3', \$(vStg3Color),

If(Opportunities.StageNumber = '2', \$(vStg2Color),

If(Opportunities.StageNumber = '1', \$(vStg1Color)))))))

The variables are in the form of:

=rgb(176,202,142)

Unfortunately, if I filter on a year (FiscalYearName) outside of the set analysis, it wrecks the colors (this has FY13 selected):

I've tried with and without the '=' in the variables and also by using the RGB directly in the If statement in the background color.

Any thoughts on how I can fix it?

Mike

• ###### Re: Expression Attribute Background Color problem

I didn't have any takers on this last month... Anybody have thoughts on this?

• ###### Re: Expression Attribute Background Color problem

Your selection might be affecting the Background color expression. Its excluded in the actual expression but not in the Background expression. Please check and make sure.

• ###### Re: Expression Attribute Background Color problem

Hi Ajay,

Thanks for the quick response!

How do I ignore that selection in the 'IF' statement for the background color... Or is there a better way to do the background color?

mike

• ###### Re: Re: Expression Attribute Background Color problem

Try like this:

If(Opportunities.StageNumber = '5',Only({<FiscalYearName =>} \$(vStg5Color)),

If(Opportunities.StageNumber = '4B', Only({<FiscalYearName =>}\$(vStg4BColor)),

If(Opportunities.StageNumber = '4A', Only({<FiscalYearName =>}\$(vStg4AColor)),

If(Opportunities.StageNumber = '3',Only({<FiscalYearName =>} \$(vStg3Color)),

If(Opportunities.StageNumber = '2', Only({<FiscalYearName =>}\$(vStg2Color)),

If(Opportunities.StageNumber = '1', Only({<FiscalYearName =>}\$(vStg1Color))))))))

Attached is a simple example which uses the same concept.

Thanks

• ###### Re: Expression Attribute Background Color problem

I would rather use the only() function with the set expression around the field than the constant number:

= If( only( {1} Opportunities.StageNumber) = '5', \$(vStg5Color),

...)

• ###### Re: Expression Attribute Background Color problem

You guys are genius! Everytime I think I know what I'm doing, ya'll come up with something that had never crossed my mind!

THANK YOU!

Mike