12 Replies Latest reply: Dec 20, 2013 2:52 PM by Brian Garside RSS

    Conditional formatting in Pivot table not working propoerly

    Brian Garside

      I have a Pivot table that displays bandwidth as a percentage 0-1. I have bands of colors 5 of them based on .2 intervals.

       

      For whatever reason my formula is not treating the colors equally. If I use a single column straight table it works fine.

      In some cases a value done not get any color.

       

      Unfortunately I can not upload a QVW on this one since its a customer.

       

      Bandwitdht ranges usually in increments of 1.6 but once summed up you get larger values but the max rang is 95 on any given hour.

      vDayScale = 30.42

      MaxBandWidthRange = 95

       

      eBandWidth = (Sum(BandWidth) / vDayScale) / MaxBandWidthRange

       

      If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
      If( $(eBandWidth) >= .8 and $(eBandWidth) <= .99 , RGB(40,140,255) ,
      If( $(eBandWidth) >= .60 and $(eBandWidth) <= .79 , RGB(75,155,255) ,
      If( $(eBandWidth) >= .40 and $(eBandWidth) <= .59 , RGB(110,175,255) ,
      If( $(eBandWidth) >= .20 and $(eBandWidth) <= .39 , RGB(145,195,255) ,
      If( $(eBandWidth) >= .01 and $(eBandWidth) <= .19 , RGB(221,240,255)
            )  )  )  )  )  )

       

      2013-12-20_132614.png