Format number to 2 DPs

SpeedySteven
SpeedySteven Member
edited December 2023 in General

I have some data that I'm importing and I want to convert to currency format nnn.nn - there doesn't appear to be a way to do this in aperture. Any ideas? I've tried the Round function but no joy - I want to convert 28 to 28.00 etc.

Best Answer

  • Henry Simms
    Henry Simms Administrator
    Answer ✓

    @SpeedySteven I noticed that the re-usable formula was exported from a Data Studio 2.4.2 environment. These packages are typically not backwards-compatible, so if you have an earlier version of Data Studio you will probably find that you can't import. Any version 2.4.2 or later should be fine.

Answers

  • Henry Simms
    Henry Simms Administrator
    edited September 2021
    Hi @SpeedySteven , there's a good discussion about this problem here: https://community.experianaperture.io/discussion/668/transform-to-decimal

    The last proposed solution in the thread is probably the most complete.
  • SpeedySteven
    SpeedySteven Member

    Thanks Henry - that's really helped - I can't seem to save the re-usable formula though

  • SpeedySteven
    SpeedySteven Member

    Thanks Henry - we need to upgrade

  • Dom Gittins
    Dom Gittins Member

    @SpeedySteven This function ought to do what you want, without any need to upgrade anything.

    2DP Formatter.png

    How it works ...

    This function takes an input parameter (called inNumber) 

    Multiply inNumber by 100 and round it to zero decimal places (using Half-Even rounding)

    Save the rounded number as a variable "RoundNum"

    We're then going to chop this number up into 2 bits - the right two digits (let's call that RightBit) and everything else (LeftBit).

    We then concatenate these two bits either side of a decimal point and save it in the variable outString : LeftBit.RightBit

    This works for any number except zero, which ends up as ".0", so we have to handle zeroes as a special case, so we check to see if the outString = ".0" and if it does we return "0.00", if not we return outString

    Test Data

    2DPFormatter_TestData.png


  • Dom Gittins
    Dom Gittins Member

    @SpeedySteven Here's a function that does what you want without needing to upgrade anything.

    2DP Formatter.png

    How it Works

    This function takes an input parameter (called inNumber) 

    Multiply inNumber by 100 and round it to zero decimal places (using Half-Even rounding)

    Save the rounded number as a variable RoundNum

    We're then going to chop this number up into 2 bits - the right two digits (let's call that RightBit) and everything else (LeftBit).

    We then concatenate these two bits either side of a decimal point and save it in the variable outString = LeftBit.RightBit

    This works for any number except zero, which ends up as ".0", so we have to handle zeroes as a special case, so we check to see if the outString = ".0" and if it does we return "0.00", if not we return outString

    Test Data

    2DPFormatter_TestData.png


  • Dom Gittins
    Dom Gittins Member

    @SpeedySteven This function does what you want without needing to upgrade anything.

    2DP Formatter.png

    How it Works

    This function takes an input parameter (called inNumber) 

    Multiply inNumber by 100 and round it to zero decimal places (using Half-Even rounding)

    Save the rounded number as a variable RoundNum

    We're then going to chop this number up into 2 bits - the right two digits (let's call that RightBit) and everything else (LeftBit).

    We then concatenate these two bits either side of a decimal point and save it in the variable outString (LeftBit.RightBit)

    This works for any number except zero, which ends up as ".0", so we have to handle zeroes as a special case

    So we check to see if the outString = ".0" and if it does we return "0.00", if not we return outString

    Test Data

    2DPFormatter_TestData.png


  • Marco_13112001
    Marco_13112001 Member

    I' m trying to use the function but and I'm receiving the following error message.

    " 0 isn't a valid scale"

    Any ideas to why is it happening?

    Many thanks in advance

  • Henry Simms
    Henry Simms Administrator

    Hi @Marco_13112001 , I wasn't able to recreate your issue using the function described above. Could you attach an exported dmx of the function you are trying to use, or screenshot it?

    Thanks, Henry

  • Marco_13112001
    Marco_13112001 Member

    @Henry Simms thanks for your reply but after a more thorough research I was able to run it by using the Pad number 2dp function, which gave the same results that I was looking for.

Categories