Issue with numbers as text

I am having the below issue with a number as text.  it needs to be displayed as text, for posting into another system. 

 

in the attached, in IA local I am just using TEXT(ROUND('Investor Amount (Local)' * 100, 0) / 100)  but as you can see on one line I get the number displayed in an odd format.

 

I have tried rounding all over the place and not sure how to rid of it.

 

text issue.jpg

Answers

  • Hi There,

     

    I haven't been able to find a fix, but it looks like as the number gets to a certain size it'll automatically set it to scientific notation form (just a guess here, someone on the forum can correct me as to what the functionality should be).

     

    I'm making an assumptions in that you expecting to copy directly from Anaplan and paste it into another system. As a possible workaround/alternative:

    -  Would you be able to export out the numbers into excel and then copy into the other system

    -  Control and paste the module into excel, format the cell and then copy it into another system

     

    Regards,

    Winston

  • @alismith ,

    Strangely multiplying by 100 and dividing the number by 100 after Round function is causing the issue. Just try by applying TEXT(ROUND('Investor Amount (Local)' 0) )
    Regards,
    Rashmi
  • Hi @alismith 

     

    You can try this:

    Create one more line item to rounded the value then convert to text in a separate line item

    Screenshot_74.png

     

    Hope this will helps!

     

    Thanks

    Akhtar

     

  • Hi @alismith 

     

    On my organisations model we had this exact same issue. 

    We went for an approach similar to @Akhtar.shahbaz.

     

    A useful link for decimal places:

    https://help.anaplan.com/anapedia/Content/Modeling/Optimizer/precision-and-the-8-decimal-point-limitation.htm

     

    Thanks,

     

    Usman

  • @alismith 

     

    This has been an issue for quite sometime and off lately Anaplan has come up with the additional syntax parameter of method in ROUND functionality - NORMAL or EXACT , here EXACT does the trick. You can take the approach @Akhtar.shahbaz  has suggested. But I would like to elaborate on the issue and  let's see if this should be the real behaviour from Anaplan. Post/Link  @usman.zia  has shared about 8 digit precision seems to be valid for such cases including optimizer but let me share my views on the below issue - If we

     

    Input 7 digit number with 0 decimal places - why does anaplan take one decimal place value when decimal places are set to be zero?

    Misbah_0-1582784153932.png

    Input 8 digit number with 0 decimal places - Although the number looks fine in the Cell but in the backend Anaplan stores it in Scientific notation

    Misbah_1-1582784240797.png

     

    Input 7 digit number with 3 decimal places - Looks fine

    Misbah_2-1582784370705.png

    Input 8 digit number with 3 decimal places - Scientific notation

    Misbah_3-1582784475699.png

     

    Well it really comes down to 8 digit number threshold (Decimal places don't matter) - post which it changes it to scientific notation. 

     

    Misbah

  • Hi @Akhtar.shahbaz 

     

    How exactly did you round?  As it doesn't work for me 

     

    and @Misbah if i use the exact rounding, i still get the forced scientific displaying of the number.  I can get rid of it if i round by 0, but that isn't an acceptable result.  

     

    I just did this as a raw module, with the number 17,121,493.40 as a hard coded number, and i still get the issue.

     

    Annotation.png

    thanks

  • @alismith 

     

    You doing TEXT(Number) or TEXT(Rounded? I think you are doing it former, try latter i.e., TEXT(Rounded)

  • @Misbah 

     

    No I am doing Text(rounded) as Akhtar had done.

    Annotation2.png

  • Hi @alismith 

     

    It would be better to round the number without decimal and then convert into text, basically when you try to convert decimal number to text it will show you scientific number.

    So i have rounded the number first without any decimal and then convert into text. I have attached the blueprint of my module for your reference.

    Screenshot_75.png

     

    Screenshot_76.png

     

     

    Hope this help!

     

    Thanks

    Akhtar

  • Hi @alismith 

     

    Another workaround for your problem is to import the value directly into text formatted line item.

    1. Save the view with two line item (make sure remove parent if any in your hierarchy)

    2. create new import action.

     

    Screenshot_78.png

     

    Hope this helps!

     

    Thanks

    Akhtar

     

  • Thanks for the tips guys

     

    I actually thought of a solution myself, that gives me the decimals I wanted.

     

    basically, I take the number i want, say  1,234,567,890.123 tale away a rounded to zero version,  leaving 0.123

     

    I then do as @Akhtar.shahbaz suggested using the text version of the numbered rounded to zero .  and then to add in the decimals i make a text version of it, shortening it by the len-1 of the string.

     

    A bit convoluted but it seems to work, and can work for any number of decimals.  you just modify the first round function to be the number you want.  And you need the Round of the two rounded functions, otherwise anaplan doesn't do the sum correctly 

    solution 1.jpg

    solution 1a.jpg