Announcements
cancel
Showing results for
Did you mean:
Creator III

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

1 Solution

Accepted Solutions
MVP

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

...)

6 Replies
Creator III
Author

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

Not applicable

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.

Creator III
Author

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

Not applicable

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

MVP

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

...)

Creator III
Author

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

Community Browser