AGGR(Max()) help please

Fri, 20 Apr 2012 04:30:24 GMT

Here is the expression in the Background color of my WOS field in my pivot table. I have null fields that show up randomly in the pivot table and cannot rely on the [WOS Goal] field to be there for a given cell in the pivot table. The cell to be formatted is the WOS cell in the pivot table and it will have a calculation in it that has nothing to do with the [WOS Goal], but the formatting needs to be based on the relationship between WOS and [WOS Goal] as is listed below. When the [WOS Goal] field is null in a given cell, the formatting goes away. I'm trying to find a way to carry the maximum [WOS Goal] value across the [Production Date] dimension to have the value there for the null fields.

The only problem I see with this formula is that the results only function on the first two columns of a 52 column pivot table. Any ideas that you can give me on how I can get this formula to have the same effect on the remaining 50 cells of the WOS pivot table would be fantastic.

IF([WOS]>(AGGR(MAX([WOS Goal]),[Customer Style], [Customer Size], [Customer Color])+8),RGB(60,105,225),
IF([WOS]<(AGGR(MAX([WOS Goal]),[Customer Style], [Customer Size], [Customer Color])-8),LightRed(),
IF([WOS]<(AGGR(MAX([WOS Goal]),[Customer Style], [Customer Size], [Customer Color])-4),Yellow(),)))

Re: AGGR(Max()) help please

Wed, 05 Jun 2013 16:04:05 GMT

I found the answer to my problem. I will post it below.

IF([WOS]>ONLY(TOTAL<[WOS Goal]>[WOS Goal])+8,RGB(60,105,225),
IF([WOS]<ONLY(TOTAL<[WOS Goal]>[WOS Goal])-8,LightRed(),
IF([WOS]<ONLY(TOTAL<[WOS Goal]>[WOS Goal])-4,Yellow(),)))

This solved the problem and carried the correct formatting all the way across the pivot table regardless of the absence of data in a given week.

topic Re: AGGR(Max()) help please in QlikView

AGGR(Max()) help please

Re: AGGR(Max()) help please