topic Re: Expression Attribute Background Color problem in QlikView

Expression Attribute Background Color problem

mikecrengland — Tue, 01 Apr 2014 22:04:51 GMT

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

Re: Expression Attribute Background Color problem

mikecrengland — Tue, 06 May 2014 18:31:37 GMT

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

Re: Expression Attribute Background Color problem

Tue, 06 May 2014 18:51:09 GMT

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.

Re: Expression Attribute Background Color problem

mikecrengland — Tue, 06 May 2014 18:56:07 GMT

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

Re: Re: Expression Attribute Background Color problem

Tue, 06 May 2014 22:10:48 GMT

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

Re: Expression Attribute Background Color problem

swuehl — Tue, 06 May 2014 22:27:47 GMT

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

...)

Re: Expression Attribute Background Color problem

mikecrengland — Wed, 07 May 2014 15:42:59 GMT

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

Re: Expression Attribute Background Color problem

zyn — Tue, 15 Jul 2025 15:46:39 GMT

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