4 Replies Latest reply: Sep 25, 2015 9:01 AM by Gysbert Wassenaar RSS

    Formating: Using fabs() and num() functions in text object

    Nelson Lam

      Dear fellow peers,

       

      Table and charts formatting can done easily in 'number' tab, however when it comes to text object all expression has to be scripted for the desired result.

       

      Has anyone encounter any issue using both function combination in a text object? It seem that both functions does not gel up mutually.

       

      Current result is not showing absolute value:

      2.jpg

       

      I am trying to achieve the conditions:

       

      1. absolute value (using fabs)
      2. percentage format (using num)
      3. negative value with brackets using nested if statements.


      Hence, my expressions might be a little heavier to read.

       

      if(v%varhc<0,

      '('&   
      //bracket
      fabs(num((
      IF(Dim2_ID='A',sum(Value),
      IF(Dim2_ID='B',sum(Value2),
      IF(Dim2_ID='C',sum(Value3),
      IF(Dim2_ID='D',sum(Amount)))))
      -(
      sum( Amount)
      ))
      /
      IF(Dim2_ID='A',sum(Value),
      IF(Dim2_ID='B',sum(Value2),
      IF(Dim2_ID='C',sum(Value3),
      IF(Dim2_ID='D',sum(Amount))))))
      ,'#,##0%')) //% format
      &')',       //bracket

      v%varhc)


      **Note: v%varhc=IF(Dim2_ID='A',sum(Value),
      IF(Dim2_ID='B',sum(Value2),
      IF(Dim2_ID='C',sum(Value3),
      IF(Dim2_ID='D',sum(Amount)))))
      -(
      sum( Amount)



      Thanks for the advice.

       

      Regards,

      Nelson