2 Replies Latest reply: Nov 18, 2015 7:10 AM by Rhona Corcoran RSS

    variable used with expression to change background color not working

    Rhona Corcoran

      my two variables are:

      vMH3 10%

      if(Year=2015,num(avg({<TargetCode={'MH-3'},TargetReferredYYYY={2015} >}[AnnualTarget])/100*0.90,'##0.0%'),
      if(Year=2014, 'not av',
      if(Year=2013, 'not av')))

       

      vMH3 5%

      if(Year=2015,num(avg({<TargetCode={'MH-3'},TargetReferredYYYY={2015} >}[AnnualTarget])/100*0.95,'##0.0%'),
      if(Year=2014, 'not av',
      if(Year=2013, 'not av')))

       

      My expression in the background colour of the column in my pivot table is:

       

      if(num(Sum({$<[POA]={3,5,7,9,11,13,17,19,21,23,25,27,4,6,8,10,12,14,18,20,22,24,26,28}>}ActualValue)/
      (
      Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30}>}ActualValue)), '##0%')>=[v MH3 5%],RGB(81,173,152),
      if(num(Sum({$<[POA]={3,5,7,9,11,13,17,19,21,23,25,27,4,6,8,10,12,14,18,20,22,24,26,28}>}ActualValue)/
      (
      Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30}>}ActualValue)), '##0%')<[vMH3 10%],RGB(208,62,81),
      if(num(Sum({$<[POA]={3,5,7,9,11,13,17,19,21,23,25,27,4,6,8,10,12,14,18,20,22,24,26,28}>}ActualValue)/
      (
      Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30}>}ActualValue)), '##0%')>=[vMH3 10%] and
      num(Sum({$<[POA]={3,5,7,9,11,13,17,19,21,23,25,27,4,6,8,10,12,14,18,20,22,24,26,28}>}ActualValue)/
      (
      Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,4,6,8,10,12,14,16,18,20,22,24,26,28,29,30}>}ActualValue)), '##0%')<[v MH3 5%],RGB(233,174,17))))

       

      It is just changing the background to red:

       

      Any ideas why its not working would be great.