Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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),
...)
I didn't have any takers on this last month... Anybody have thoughts on this?
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.
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
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
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),
...)
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