4 Replies Latest reply on Dec 11, 2018 8:26 AM by Laurence Turner

# Dynamically change number format with parameter data-type choice

Hi All,

I have a parameter that allows the user to choose between four measures are shown on a bar chart, this is applied using the following calculated field:
IF [Data Type] = "USD, millions"

THEN [Value]

ELSEIF [Data Type] = "Number of Records"

THEN SUM([Number of Records])

ELSEIF  [Data Type] = "Per cent of total value, USD, millions"

THEN [Per cent, value]

ELSEIF [Data Type] = "Per cent of total number of records"

THEN [Per cent, number of records]

END

I would like to show each of these data types with different levels of decimal precision:

- Value (USD, millions) 0 DP
- Number of Records 0 DP
- Per cent of total value 2 DP

- Per cent of number of records 2 DP

Is there a way to manipulate a calculated field to do this?

Thanks,
Laurence

• ###### 1. Re: Dynamically change number format with parameter data-type choice

Hi Laurence,

There is an article that have described what you might just need. The idea is to put negative sign in front of those measures which you want to be in percentage. This allows you to custom format those numbers.

• ###### 2. Re: Dynamically change number format with parameter data-type choice

If you are wanting to set the number of decimal places then the best thing I have found to do is convert the values to string and then use the Left function to display only the part you want.  In order for this to work in any totals or grand totals, you will also have to convert back to a number when doing the calculations.  So if your value is 23.12345 and you want two decimal places, you need to say str([value],5).  You may also want to look at the sixth character and determine whether or not the fifth character needs to be increased by 1.

• ###### 3. Re: Dynamically change number format with parameter data-type choice

Hi Tim

Thanks for the tip - much appreciated.

I have to say it would be a very useful addition to have some sort of function that allows you to do this, and therefore could be used in a calculation.

Best,
Laurence