Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecrengland
Creator III
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.

4-1-2014 3-54-01 PM.jpg

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

4-1-2014 3-58-09 PM.jpg

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

1 Solution

Accepted Solutions
swuehl
MVP
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),

 

...)



View solution in original post

6 Replies
mikecrengland
Creator III
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.

mikecrengland
Creator III
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

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

 

...)



mikecrengland
Creator III
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