Expression Attribute Background Color problem

[mikecrengland]

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?

Thanks in advance!

Mike

www.fortunecookiebi.com

[mikecrengland]

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

[#3]

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.

[mikecrengland]

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

[#5]

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

[swuehl · Accepted solution]

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

...)

[mikecrengland]

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

[zyn]

I have a followup questio @swuehl,

i am going through a similar problem, and when i am doing selection my background colour is changing,

below is my expression can you suggest for this please:

=If(
(
(Sum({<GITDATE = {'$(=date(max(GITDATE)))'},Table_Flag = {'TBA'},Measure>}PRMCNT) ) <= sum({<GITDATE,Measure>}Threshold_Green))
and (WildMatch(Measure,'GRM','BCL',MNB')), LightGreen()
,'#ffbf00'
)