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


      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.


      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.


      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, 
                If([% Acum. Ud]<0.8, 


      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.