2 Replies Latest reply on Jul 13, 2018 2:11 PM by Deepak Rai

    HELP! Conversion of Numbers to String Issue

    Will Perkins

      When formatting a number to a string after doing division, I do not always get consistent results. I have a calculation that "dynamically" formats the value of a financial field. I often have vast differences of amounts to format so I can't use a field format for all of them.

       

      My formula checks to see if the values are greater than a million, thousand, then the rest.

      IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

          "$" + LEFT(STR(ROUND([Sales 2018] / 1000000,2)),4) + "MM"

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

          "$" + LEFT(STR(ROUND([Sales 2018] / 1000,2)),4) + "K"

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

          //Removes decimal places

          "$" + LEFT(STR(ROUND([Sales 2018] / 1,2)),3) + ""

      ELSE

          "TBD"

      END

       

       

      The formula in the image "2018 Sales (Pre Format)" provides me the expected results prior to converting it to a string.

      IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

          ROUND([Sales 2018] / 1000000,2)

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

          ROUND([Sales 2018] / 1000,2)

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

          //Removes decimal places

          [Sales 2018]

      END

       

      The formula in the image "2018 Sales (STR)" does not provide me the expected results when converting it to a string.

      STR(IF LEN(STR(ROUND([Sales 2018],0))) > 6 THEN

          ROUND([Sales 2018] / 1000000,2)

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) > 3 THEN

          ROUND([Sales 2018] / 1000,2)

      ELSEIF LEN(STR(ROUND([Sales 2018],0))) <= 3 THEN

          //Removes decimal places

          [Sales 2018]

      END)

       

      Capture.JPG

       

      Attached is a mockup of what I put together.