Converting numbers to text without losing decimals

hajnalkasostai
hajnalkasostai
edited August 2023 in Modeling

Hi!

I am trying to create a reporting module with mixed line items (numbers and percentages) so I am converting the numbers to text for display purposes only.

We need 1 decimal and have very small numbers eg. 0.0001 which should then be displayed as 0.0 however during the process the decimal is lost.

Same issue with integers: they are displayed as 3 instead of 3.0

This is how it is in Anaplan currently:
- ACT is text using TEXT(ACT1)
- ACT1 is in number format

image.png



Thank you for your help in advance!

Answers

  • HomlodiCinti
    HomlodiCinti

    I would also like to know the answer : )

  • TristanS
    TristanS

    @hajnalkasostai If it's only happening for 0 values you can add a condition to your conversion. Probably not what you were hoping for but does the job.

    if TEXT(ACT1) = "0" then "0.0" else TEXT(ACT1)

  • HomlodiCinti
    HomlodiCinti

    Unfortunately it isn't always 0, but it does work as you suggested

  • TristanS
    TristanS

    @HomlodiCinti ah are you getting stuff like "1". What you can do then is this function

    if FIND(".", TEXT(ACT1)) = 0 then TEXT(ACT1) & ".0" else TEXT(ACT1)

Categories