# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Contributor 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?

Thanks in advance!

Mike

www.fortunecookiebi.com

Tags (1)
1 Solution

Accepted Solutions
MVP

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

...)

6 Replies
Contributor III

## Re: Expression Attribute Background Color problem

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

Not applicable

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

Contributor III

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

Not applicable

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

MVP

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

...)

Contributor III

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