Shorter "Background Color" Expressions?

[TKendrick20]

I have a bar chart with one dimension and measure and I would like to color each bar according to its expression value. The expression is:

AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])

When I go to style the Background Color, I have this obnoxious expression:

IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])=0,$(vYellow),

     IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])>0,

          IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])<=2,

               IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])>1,$(vLightGreen),$(vGreenYellow)),$(vGreen)),

          IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])>=-2,

               IF(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])<-1,$(vOrange),$(vOrangeYellow)),$(vRed))))

Is there a way I can build this definition without repeating the expression 6 times? Am I missing some EXPRESSIONVALUE function or something that would make this considerably more manageable (I have other definitions that are much worse)?

[#2]

Hello Tim,

Try using variables with parameters. Read this: Variables with Parameters

BR,

Kuba

[mphekin12]

You could create a variable for you average expression and use the variable in your background color expression instead of the longer average expression.

[jpenuliar]

or try Pick & Match combo

[rwunderlich · Accepted solution]

The EXPRESSIONVALUE function you are thinking of is just the label of the Expression Column. So assume you assigned the label "Average Choice" to this expression. Then your background color expression could refer to the column value like:

IF([Average Choice]=0,$(vYellow),

     IF(([Average Choice])>0,

          ...etc

P.S. As an alternative, you can also use the Column(n) function to refer to a column value.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[sunny_talwar]

How about this:

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) < -2, $(vRed),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) < -1, $(vOrange),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) < 0, $(vOrangeYellow),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) = 0, $(vYellow),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) < 1, $(vGreenYellow),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) < 2, $(vLightGreen),

If(Avg({<[Question Response Group ID]={"5"}>} [Answer Choice Value]) >= 2, $(vGreen))))))))

[TKendrick20]

YES!!!

Does this technique have a name?

[rwunderlich]

It may have a formal name within R&D, but I've always called it "column label reference". For more info see

Qlikview Cookbook: Tutorial - About Column Visibility http://qlikviewcookbook.com/recipes/download-info/tutorial-about-column-visibility/

Please mark something as correct to close the thread if this works for you.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[TKendrick20]

Beautiful, thank you very much!

Any idea how something like this could work in Qlik Sense, too?

[Anil_Babu_Samineni]

I am not sure is this Calculated Dim / Expression

vYellow = Yellow()

vLightGreen = LightGreen()

vGreenYellow = LightBlue()

vGreen = Green()

vOrange = LightMagenta()

vOrangeYellow = LightCyan()

LET vAverage = AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])

IF($(vAverage)=0,$(vYellow),

     IF($(vAverage)>0,$(vLightGreen),

          IF($(vAverage)<=2,$(vGreenYellow)),

               IF($(vAverage)>1,$(vGreen)),

          IF($(vAverage)>=-2,$(vOrange),$(vOrangeYellow))))))