topic Re: Background col0r condition + pivot in QlikView

Background col0r condition + pivot

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

Hi,

I have used the below condition to get the color but it is not working. Please have a look.

Condition:

=if((aggr(num((sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales)) /(sum({<Date={$(varMaxDate)},flag={'Wet Sign'}>} Sales)+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales) +sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Not Complete'}>} Sales)),' #,##0.0%'),Loc,A7BANB))*100 >=70

and (aggr(num((sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales)) /(sum({<Date={$(varMaxDate)},flag={'Wet Sign'}>} Sales)+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales) +sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Not Complete'}>} Sales)),' #,##0.0%'),Loc,A7BANB)*100) <90,RGB(252,219,8) ,

if((aggr(num((sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales)) /(sum({<Date={$(varMaxDate)},flag={'Wet Sign'}>} Sales)+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales) +sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Not Complete'}>} Sales)),' #,##0.0%'),Loc,A7BANB)*100)>=90,rgb(0,238,0),

Thanks..

Re: Background col0r condition + pivot

sunny_talwar — Fri, 20 Jan 2017 15:41:29 GMT

Just by looking at this humongous expression we are suppose to know what is wrong with it and why it isn't working? All the best bro... I hope someone can really decipher this for you

Re: Background col0r condition + pivot

nareshthavidishetty — Mon, 23 Jan 2017 14:03:47 GMT

Hi,

Only few cells were highlighted.Please find below.

Thanks..

Re: Background col0r condition + pivot

stabben23 — Mon, 23 Jan 2017 15:01:38 GMT

Why not use Visual Cues tab and your already calculated expression?

Re: Background col0r condition + pivot

stabben23 — Mon, 23 Jan 2017 15:08:05 GMT

Like this:

Re: Background col0r condition + pivot

MK9885 — Mon, 23 Jan 2017 15:44:24 GMT

Maybe you can use Custom Format Cell to give your color expression?

Enable the 'Design Grid' from the view option on the top left. Then right click on the column you want to change the color.

Give your color expression there and check if it works?

And if you can add the below expression to a Variable?

vExp =

and for defining the color use

if($(vExp)<70,rgb(255,69,0))

Re: Background col0r condition + pivot

nareshthavidishetty — Mon, 23 Jan 2017 17:51:21 GMT

Hi,

I have done the same but it's not working.

Thanks..

Re: Background col0r condition + pivot

neelamsaroha157 — Mon, 23 Jan 2017 19:27:28 GMT

I think in your dimension you have 3 fields and you are using only Two fields in aggr part of the expression, that is why the color is only at aggr of first two columns.

Re: Background col0r condition + pivot

rwunderlich — Mon, 23 Jan 2017 19:46:59 GMT

I agree with Staffan that Visual Cues is probably the easiest and best approach here.

But suppose you can't use Visual Cues because you are doing something more complicated or using more colors than Visual Cues allows. I'll assume the expression calculating the value in your background color is the same as your chart expression, Assume that expression is labeled "Margin". In that case, you can simply refer to the label of the expression in your background color:

if(Margin * 100 > 90, green(),

,if(Margin * 100 > 70, yellow()

,red()))

If you don't want to depend on the label name, then you can use to the Column() function to refer to the nth expression:

if(Column(1) * 100 > 90, green(),

,if(Column(1) > 70, yellow()

,red()))

Much more efficient than repeating the expression,

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Background col0r condition + pivot

nareshthavidishetty — Tue, 24 Jan 2017 01:23:16 GMT

Hi,

I have used it as an Calculated Dimension.

Thanks..