Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Persistent colour not working with calculated dimension

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.

picture1.PNGpicture2.PNG

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)

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

  2,

  3

  )

)

,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

View solution in original post

9 Replies
sunny_talwar

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

Not applicable
Author

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

Not applicable
Author

Have you checked the sample?

sunny_talwar

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

Best,

Sunny

vinieme12
Champion III
Champion III

also check Repeat Last Color and try

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

  2,

  3

  )

)

,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

Not applicable
Author

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 !

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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