12 Replies Latest reply on Apr 12, 2019 6:15 AM by Toby Erkson

# How to custom format a calculate field to show numbers as well as strings

Hi All,

I have a calculated field which is currently formatted as a percentage field.

I want to custom format in such a way that if the value is >100% it should be "A" and if it is >-100% it should be "B" else the calculated values.

Any help is highly appreciated.

Thank you

• ###### 1. Re: How to custom format a calculate field to show numbers as well as strings

Try the below formulae

IF [PercentageFIeld] >=1 THEN 'A'

ELSE 'B'

END

• ###### 2. Re: How to custom format a calculate field to show numbers as well as strings

If the percentages are not >100% and not >-100% I want to show the values.

I believe with the above formula we cannot achieve that.

• ###### 3. Re: How to custom format a calculate field to show numbers as well as strings

Hi Sanju

below Formula might help you to solve your query

IF [Percenatge] >=100 Then 'A' Else STR([Percenatge]) END

Regards,

Anupam

• ###### 4. Re: How to custom format a calculate field to show numbers as well as strings

Since one field can only be classed as one data type and you would like both string and numeric the best workaround i can think of would be to convert the number (percentage field) to a string value inside the formula where the percentage > 100% or <-100%

The formula would be similar to that below:

IF [PercentageField] > 1 THEN 'A'

ELSEIF [PercentageField] < -1 THEN 'B'

ELSE STR([Year of Order Date]) + '%'

END

Therefore a value of 101% would return A, a value of -102% would return B and a value of 56% would return 56%

If you have any questions please let me know,

Thanks!

• ###### 5. Re: How to custom format a calculate field to show numbers as well as strings

Hi Jonathan,

The value which is like 56.66 is showing me all the decimals like 56.66678945% when I am trying to convert it in string percentage.

Any idea how we can change this to 56.6%?

Thank you,

• ###### 6. Re: How to custom format a calculate field to show numbers as well as strings

Hi Vibin,

Just need to know whether 100% in your "PercentageField" is stored in the raw data source as 100 or 1?

Thanks!

• ###### 7. Re: How to custom format a calculate field to show numbers as well as strings

Hi Jonathan,

The 100% field is actually a calculated field.

So the output from the calculate field is like 1.0 which I am using the format option to change to 100%.

I see there is an option in the custom format, but I am unable to achieve the result.

Thank you

• ###### 8. Re: How to custom format a calculate field to show numbers as well as strings

Just the one decimal place?

• ###### 9. Re: How to custom format a calculate field to show numbers as well as strings

yes one decimal place.

• ###### 10. Re: How to custom format a calculate field to show numbers as well as strings

Hi Sanju,

Please provide sample workbook so I can give a try

Regards,

Anupam

• ###### 11. Re: How to custom format a calculate field to show numbers as well as strings

We just need to change the calculation to:

IF ATTR([Percentage Field]) > 1 THEN 'A'

ELSEIF ATTR([Percentage Field]) < -1 THEN 'B'

ELSE STR(ROUND(ATTR([Percentage Field]*100),1)) + '%'

END

If you have any other questions just let me know,

Thanks!

• ###### 12. Re: How to custom format a calculate field to show numbers as well as strings

Why not build upon the answer to your basically same question you asked here:  Calculation field output / result with string and integer datatype