Dimension Background Colour in a Pivot Table

[#1]

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?

[giakoum · Accepted solution]

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.

[#3]

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

[#4]

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.