1 Reply Latest reply: Jun 5, 2013 12:04 PM by gr8scott RSS

    AGGR(Max()) help please

      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

          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.