Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

Shorter "Background Color" Expressions?

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Shorter "Background Color" Expressions?

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

View solution in original post

18 Replies
Not applicable

Re: Shorter "Background Color" Expressions?

Hello Tim,

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

BR,

Kuba

mphekin12
Valued Contributor

Re: Shorter "Background Color" Expressions?

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

Partner
Partner

Re: Shorter "Background Color" Expressions?

or try Pick & Match combo

MVP & Luminary
MVP & Luminary

Re: Shorter "Background Color" Expressions?

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

View solution in original post

Re: Shorter "Background Color" Expressions?

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

Partner
Partner

Re: Shorter "Background Color" Expressions?

YES!!!

Does this technique have a name?

MVP & Luminary
MVP & Luminary

Re: Shorter "Background Color" Expressions?

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

Partner
Partner

Re: Shorter "Background Color" Expressions?

Beautiful, thank you very much!

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

Re: Shorter "Background Color" Expressions?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)