Visual Cues

[#1]

Hello,

I have a pivot table which looks something like below .

MeasureType     MeasureName     Variance

Revenue               Sales                    200

Expense               COGS                   -19

Expense               Var Exp                 -30

Revenue               Marg Contribution     140

Expense               Fixed OH                 30

Expense               Marketing                    -140

Revenue               Profit                         -20

I want to use Visual Cues in the Variance column. The Red or Green colour is not only dependent on whether the Variance is a positive or negative number but also if the MeasureType is an Revenue or Expense item.

How can I use visual cues, to write an expression like :

If(AND(MeasureType=Revenue,Variance>=0, green(),If(AND(MeasureType=Revenue,Variance<0, red(), etc.

Appreciate any help.

Thanks.

Jay

[Nicole-Smith]

You don't want to do it in Visual Cues.  Instead, click on the plus sign next to your Variance expression.  In background color, you can put something like:

if(MeasureType='Revenue' and Variance>=0, green(),

if(MeasureType='Revenue' and Variance<0, red()))

[#3]

You can certainly use expressions, but it might be easier to use the Visual Cues native to QlikView. Navigate to the chart/table Properties, go to the Visual Cues tab, click on the Variance expression and enter your threshold numbers for the Upper and Lower values and set the desired colors.

[#4]

Sorry, can't seem to delete the reply, but my suggestion would work on one variable, I realized after I posted that you needed to account for 2 variables. Nicole's recommendation is correct.

[#5]

Thanks Nicole.

I used :

(MeasureType='Revenue' and [Var : 2014 Act v Plan]>=0, Green(),
if(MeasureType='Revenue' and [Var : 2014 Act v Plan]<0, Red(),
if(MeasureType='Expense' and [Var : 2014 Act v Plan]>=0, Red(),
if(MeasureType='Expense' and [Var : 2014 Act v Plan]<0, Green()))))

The Variance column is not just one but 12 of them for each MonthID.

The above expresion seems to work only for the first instance it encounters.

It does not highlight any colours for the rest of the columns or rows.

Any reasons why it would do this ?

[Nicole-Smith]

Instead of using the name of the expression, try using the expression itself.