0 Replies Latest reply: Mar 5, 2013 6:25 AM by Nikki Fairgrieve RSS

    Multiple Colours within One Expression within a Chart



      I am trying to change the colour of data calculated by one expression using the following if Statement within the Text Colour dimension so that depending on the Sales Status of a Plot the calculated data is a different colour. It doesn't work the problem being because the data is held within a single cell and so it changes the text colour all to one colour.


      if([Sales Status Code]='R', Green()
           , if([Sales Status Code]='U', Blue()
           , if([Sales Status Code]= 'NR', Yellow()
           , if([Sales Status Code]= 'C', lightred()

               , if([Sales Status Code]='E', LightCyan(), Black()


      The Expression I am using is:

      sum({<[Sales Status Code={'C', 'NR', 'U', 'E', 'R'}>}[Count Plot])


      The Dimesion is:

      Site and Month


      It is difficult to explain so I have attached a mock up of what I wish to achieve. I have only managed to achieve this by using 5 layered charts and changing the expression to sum depending on the Sales Status and changing the Text Colour of each seperate chart to a specified colour e.g.


      Chart 1 - sum({<[Sales Status Code ={'R'}>}[Count Plot])

      Chart 2 - sum({<[Sales Status Code ={'R'}>}[Count Plot])

      Chart 3 - sum({<[Sales Status Code ={'R'}>}[Count Plot])

      Chart 4 - sum({<[Sales Status Code ={'R'}>}[Count Plot])

      Chart 5 - sum({<[Sales Status Code ={'R'}>}[Count Plot])


      I cannot have the Sales Status as a seperate Dimension or as Seperate Expressions within the same chart because it makes the chart far too large and almost unusable. I also cannot use the Layered chart method as the table is not a fixed size and you will need to scroll which doesn't work.


      Please can advise if this is possible?


      Kind Regards,