topic Re: Persistent colour not working with calculated dimension in QlikView

Persistent colour not working with calculated dimension

Mon, 26 Sep 2016 07:19:10 GMT

Hi,

I have an application where we have 2 similar but different calculated dimensions with 3 different fields in those dimensions. There are 3 colors assigned and in settings we have persistent colors. The problem occurs when you make a selection in on the charts then suddenly the colors jump around even though the text stays in the same order. I have tried to use background color with the same expressions but it doesn't work. I suspect it has to do with sort order where I have selected numeric value which works perfect for the text in the dimension but not for the colors. In the screenshots you see how the color jump around but not the text which stays consistent.

first calculated dimension:

if(

aggr((max([field1]) + max([field2)), field3) = 0

OR aggr(max([field4)/(max([field1]) + max([field2])), field3) >= 100,

dual('text1)', 1),

if(aggr(max([field4])/(max(field1]) + max([field2])), field3) < 100,

dual('text2', 2),

dual('text3', 3)

)

background colour:

if(

aggr((max([field1]) + max([field2)), field3) = 0

OR aggr(max([field4)/(max([field1]) + max([field2])), field3) >= 100,

RGB(0,128,64),

if(aggr(max([field4])/(max(field1]) + max([field2])), field3) < 100,

RGB(192,192,192),

RGB(255,217,47)

)

Expression:

count(DISTINCT field3) / count(DISTINCT TOTAL field3)

The other calculated dimension is similar:

if(

aggr(max([field5]), field3) = 0

OR aggr(max([field4])/max([field5]), field3) >= 100,

dual('text4)', 1) ,

if(aggr(max([field4])/max([field5]), field3) < 100,

dual('text5', 2),

dual('text6', 3)

)

Background colour2:

if(

aggr(max([field5]), field3) = 0

OR aggr(max([field4])/max([field5]), field3) >= 100,

RGB(0,128,64),

if(aggr(max([field4])/max([field5]), field3) < 100,

RGB(192,192,192),

RGB(255,217,47)

)

Expression3:

count(DISTINCT field3) / count(DISTINCT TOTAL field3)

Re: Persistent colour not working with calculated dimension

sunny_talwar — Mon, 26 Sep 2016 16:21:08 GMT

Is it possible to share a sample to look at the issue?

Re: Persistent colour not working with calculated dimension

Tue, 27 Sep 2016 11:31:39 GMT

i tried to replicate the issue with dummy data, I think I got the same issue at least

Re: Persistent colour not working with calculated dimension

Mon, 03 Oct 2016 06:34:02 GMT

Have you checked the sample?

Re: Persistent colour not working with calculated dimension

sunny_talwar — Tue, 04 Oct 2016 11:44:52 GMT

I have not, but I plan to check this sometime today.

Best,

Sunny

Re: Persistent colour not working with calculated dimension

vinieme12 — Tue, 04 Oct 2016 13:02:39 GMT

also check Repeat Last Color and try

Re: Persistent colour not working with calculated dimension

Tue, 04 Oct 2016 13:10:51 GMT

unfortunately it is not that simple, the problem is that there are 2 different pie charts that contains the same expression but different calculated dimensions. If i choose persistent color and repeat last it works fine for the one chart I am interacting with but the other one gets the wrong color order.

Re: Persistent colour not working with calculated dimension

rwunderlich — Tue, 04 Oct 2016 14:19:10 GMT

Your background color expression should be in the Expression, not the Dimension.

Repeating the Calculated dim in a color() function in your sample works for me.

=color(

aggr(

if(

aggr((max([S KF]) + max([S PM])), VIN) = 0

OR aggr(max([Total time])/(max([S KF]) + max([S PM])), VIN) >= 100

,1,

if(aggr(max([Total time])/(max([S KF]) + max([S PM])), VIN) < 100,

)

,VIN)

)

Note the addition of the outer aggr(). You should probably have that in the Dimension def as well for consistency.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Persistent colour not working with calculated dimension

Wed, 05 Oct 2016 08:08:40 GMT

Thanks Rob! That does the trick, I was going crazy over this since it seemed to be a fairly easy thing to do . I have never used the color function before so may thanks for sharing that knowledge !

Re: Persistent colour not working with calculated dimension

rwunderlich — Wed, 05 Oct 2016 18:59:38 GMT

Glad it worked. Here's another clever thing you can use color() for.

A Color Trick | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com