0 Replies Latest reply: Oct 18, 2012 6:57 AM by Chris Baldock RSS

    Formatting a calculated dimension pivot table

    Chris Baldock

      Hi All,

       

      I am creating a pivot table using two calculated dimensions which (using a number of variables) count the number of records which fall between the variables specified. The table works fine but I now want to format the background colour to specify certain colours depending on the calculated groups.

       

      An extract of the code I have for the dimension calculations is as follows:

       

      Calculated Dimension 1:

       

      = IF(CUMULATIVE_PERCENTAGE <= vKeeper and PROFIT > 0, 'Keeper',

         IF(CUMULATIVE_PERCENTAGE > vKeeper and CUMULATIVE_PERCENTAGE <= vMaintainer, 'Maintainer'))

       

      Calculated Dimension 2:

       

      = IF(Percent_Weeks_Sold >= vRunner, 'Runner',

      Percent_Weeks_Sold < vRunnerand Percent Weeks_Sold >= vRepeater, 'Repeater'))

      = IF(

       

      My expression is a simple count ( =Count(product) of all products which fall into each of these calculated categories.

       

      The pivot table produced looks as follows:

       

      RunnerRepeater
      Keeper1020
      Maintainer3015

       

      I now want to format the value cells, for example the cell relating to keeper/runner = lightgreen(), keeper/repeater = lightblue(), maintainer/repeater = lightred() etc.

      Does anyone know how I can set the colour formatting to colour the cells in line with the calculated dimension?

      Many thanks,

      Chris