0 Replies Latest reply: Jan 4, 2017 10:14 AM by sean cremin RSS

    Multiple if statements& color expression within Graphs

    sean cremin

      Hi All.  Wondering if anyone can help.

      I'm at a lose end with this.

       

      I have a group of dates within a Dimension [DISC DATE], I can categories the dates in certain groups within a table based on how many days from today until the discontinued date (I.e between 0-30 = "Action") with the following expression

       

      Category Expression-

      if(-(today() - [FF Disc Date])>0 and ([FF Disc Date]) <30, 'Action',

      if(-(today() - [FF Disc Date]) >30 and ([FF Disc Date]) >90 and right([FF Disc Date],4)<>2099, 'Attention',

      if (right([FF Disc Date],4) =2099,'Clear')))

       

      Using the background color within the table options adds the color which also works superbly

       

      Color Expression -

      if(-(today() - [FF Disc Date])<30, rgb(255,0,0),

      if(-(today() - [FF Disc Date]) >30 and ([FF Disc Date]) >90 and right([FF Disc Date],4)<>2099, rgb(255,194,0),

      if (right([FF Disc Date],4) =2099,rgb(50,205,50))))

       

      To add a high level view I'm aiming to create a pie chart, which will split Action,Attention and Clear, with the relevant color being present in the chart. Red,Amber,Green

       

      My question is, the expression for the color will not work in the Pie Chart, nor any other graphs.

      I believe it's because "action","attention"and "clear" are not grouped or present as fields as such, the color code works in the table as all values are present.

       

      I've tried the AGGR,SUM etc. functions to try and group them then add the color. I.e Group all less then 30 days and add color so to speak.

      After 3 hours of playing around with multiple expressions I am stumped, please help.