How to remove trailing zeros from a calculated field

Question & Answer
,
1

I want to create a summary of data that is being visualized using a pie chart. The summary is too specific to be using Insights.

So I created a calculated field that returns a string by concatenating, aggregated fields and the text which I want to be shown using the formula below


  toString(
  distinct_count({customer_id}) ), 

  ' customers were identified as having  multiple accounts with different banking details. There were R',  
  
  toString (
    round(
      sumIf(
        {total_amount}, 
        {account_type} = 'C'
        ), 2
      )
  ), 

  ' to R',    


  toString( 
      sumIf(
        {total_amount}, 
        {account_type} = 'D'
      )
  ),

' payments identified that are linked to ', 

  toString(
    distinct_countIf(
      {account_id}, 
      {account_type} = 'C'
    )
  ), 

' to ',  

  toString(
    distinct_countIf(
      {account_id}, 
      {account_type} = 'D'
    )
  ), 

  ' accounts.'
)

NB: The amount field is of type decimal. I also rounded the aggregated field to 2 dp as this is a currency.

When I display this field its the figures have some trailing zeros showing.

image

Is there a way I can get rid of the trailing zeros ?

2

split(field, ‘.’, 1) to get the integer part.
You can further concat the integer part with left(split(field, ‘.’, 2), 2)

1 Like