topic Dimension Background Colour in a Pivot Table in QlikView

Dimension Background Colour in a Pivot Table

Wed, 27 Jun 2012 15:05:04 GMT

I am working on an app where I want to be able to highlight a dimension based on the value of the element for the most recent month, rather than (or as well as) highlighting the individual element.

I have attached a simplified example to illustrate - the pivot I am working with has more dimensions/expressions.

Using set analysis, you can check the value for the most recent month and set the backgrond value for a condition.

  
=if(Min({<Month={"$(vMaxMonth)"}>} Sales) < 200, lightred())

This works, provided that there is a Sales value for the most recent month for every dimension value in the table. However, as soon as one dimension does not have an associated value the formatting breaks for the entire dimension (not just the occurance that has no value, which would be an acceptable for what I need).

From the example if you select months 1 to 4 the highlighting works. Select months 1 to 5, or 1 to 6 and it doesn't work. Select just month 5 or 6 and it works as it has now dropped the dimenison occurance with no associated value.

Is there a better way to code the attribute condition so that it does not try to obtain a missing value?

Re: Dimension Background Colour in a Pivot Table

giakoum — Wed, 27 Jun 2012 16:09:00 GMT

Man, that was tricky...

Hope this is what you need. if there is a null value, it considers it as less than 200, hope this helps.

Re: Dimension Background Colour in a Pivot Table

Wed, 27 Jun 2012 16:35:03 GMT

Ioannis

That very much helps, thank you. I really must look at the aggr function more closely, have never had cause to use it before.

Regards

David

Re: Dimension Background Colour in a Pivot Table

Fri, 06 Sep 2013 15:01:46 GMT

I'm trying to understand exactly what

if(Min({<Month={"$(vMaxMonth)"}>} Sales) < 200

is supposed to find.

Is it the minimum number of Sales in the last month?

One of the things I find really confusing about set analysis is the syntax, and the documentation

and white papers are not a lot of help.

Can anyone explain this for me? Thanks.