topic Re: Shorter "Background Color" Expressions? in QlikView

Shorter "Background Color" Expressions?

TKendrick20 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Shorter "Background Color" Expressions?

Thu, 28 Jul 2016 16:18:19 GMT

Hello Tim,

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

BR,

Kuba

Re: Shorter "Background Color" Expressions?

mphekin12 — Thu, 28 Jul 2016 16:21:16 GMT

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

Re: Shorter "Background Color" Expressions?

jpenuliar — Thu, 28 Jul 2016 16:25:41 GMT

or try Pick & Match combo

Re: Shorter "Background Color" Expressions?

rwunderlich — Thu, 28 Jul 2016 16:42:07 GMT

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

Re: Shorter "Background Color" Expressions?

sunny_talwar — Thu, 28 Jul 2016 16:47:48 GMT

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

Re: Shorter "Background Color" Expressions?

TKendrick20 — Thu, 28 Jul 2016 17:09:36 GMT

YES!!!

Does this technique have a name?

Re: Shorter "Background Color" Expressions?

rwunderlich — Thu, 28 Jul 2016 17:29:58 GMT

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

Re: Shorter "Background Color" Expressions?

TKendrick20 — Thu, 28 Jul 2016 17:32:51 GMT

Beautiful, thank you very much!

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

Re: Shorter "Background Color" Expressions?

Anil_Babu_Samineni — Thu, 28 Jul 2016 17:36:18 GMT

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

Re: Shorter "Background Color" Expressions?

rwunderlich — Thu, 28 Jul 2016 21:44:55 GMT

It should work the same in Qlik Sense. Have you encountered any issues?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Shorter "Background Color" Expressions?

TKendrick20 — Mon, 01 Aug 2016 14:17:32 GMT

I am not able to use the measure label as a column in Qlik Sense 3.0 as in QlikView. Perhaps there is a different syntax?

Re: Shorter "Background Color" Expressions?

rwunderlich — Wed, 10 Aug 2016 01:24:48 GMT

Tim,

I've not encountered any problem referencing column labels in Qlik Sense desktop 3.0. Can you provide an example or more detail on the problem you are seeing?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Shorter "Background Color" Expressions?

TKendrick20 — Wed, 10 Aug 2016 15:58:07 GMT

I'm on Qlik Sense Enterprise 3.0. I have a bar chart with one dimension and a Measure with the Label "Score" and the expression below.

Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])

Then, I have a huge expression for the Colors and Legends attribute that duplicates that formula many times. I am not able to substitute the field [Score] in for the formula like I can in QlikView. Is there another syntax to do this?

Re: Shorter "Background Color" Expressions?

rwunderlich — Wed, 10 Aug 2016 18:32:03 GMT

Perhaps the issue is in your color expression. Note that you can't use "Score" as a function argument. Can you pos the color expression?

-Rob

Re: Shorter "Background Color" Expressions?

TKendrick20 — Mon, 15 Aug 2016 15:11:18 GMT

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value]),vYellow,

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])>0,

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])<=2,

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])>1,vLightGreen,vGreenYellow),vScoreGreen),

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])>=-2,

IF(Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value])<-1,vOrange,vOrangeYellow),vScoreRed)))

Re: Shorter "Background Color" Expressions?

rwunderlich — Tue, 16 Aug 2016 22:57:41 GMT

Can you post the color expression you are using when you use the "Score" label? (I take it the one you posted is the one that works).

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Shorter "Background Color" Expressions?

TKendrick20 — Wed, 17 Aug 2016 16:56:54 GMT

That's the thing that doesn't work in Qlik Sense which works in QlikView. In QlikView I can substitute [Score] instead of Avg({<[Participant Group]-={"Self"}>}[Answer Choice Value]). Here I tried the exact same thing, but it does not work in Qlik Sense. I am stuck with the long version.

Re: Shorter "Background Color" Expressions?

TKendrick20 — Mon, 12 Sep 2016 15:49:03 GMT

Finally arrived at a much simpler solution for Qlik Sense. I am using the PICK and CEIL functions to build this expression:

PICK(CEIL(AVG({<[Question Response Group ID]={"5"}>} [Answer Choice Value])+4),

vRed,

vOrange,

vOrangeYellow,

vYellow,

vGreenYellow,

vLightGreen,

vScoreGreen

)

Which is notably more efficient in space and resource usage.