2 Replies Latest reply: Sep 26, 2012 5:16 AM by Álvaro J. Martínez González RSS

    Set background color ignoring the order in a pivot table

      Hi,

      I'm trying to set the backcolor to 3 columns where the first values that represent the 20% are in a dark background and the 80% are in a light background.

       

      In this image you can see my goal (done with excel). Columns are ordered by "% V" and all backgrounds are colored as I need.

      expected.jpg

      This is what I get on the QV. Here the chart is ordered by "% Ud" and the colored backgrounds are correct in this column. However, the other 2 ("% V" and "% MC") are not the correct, because they're using the top values instead of the ranked values.

      result.jpg

      To do this I use one more hidden expression to achieve the number in %, the acumulated value (Acum. XX) and it's something like:

      Ud + If(RowNo()=1,0,Above([Acum. Ud]))
      

      This is only useful for the column that I use to order by.

       

      The way I set colors to background is this:

      If([% Acum. Ud]<0.2, 
                RGB(255,255,77), 
                If([% Acum. Ud]<0.8, 
                          RGB(255,255,195)
                          )
                )
      
      

       

      I've tried diferent ways, using the function Rank() but I'm unable to achieve my goal.

       

      The files attached are this two images, just for a better resolution.

       

      Any help will be appreciated.

      Thanks in adavanced.