3 Replies Latest reply on Sep 7, 2018 12:13 PM by Rebecca LoSurdo

    Formatting Issue with a Decimal as a String

    Rebecca LoSurdo

      I am having trouble with number formatting because I need to ultimately convert my numbers to strings (for a broader purpose not included in this sample workbook). I'm running into an issue where Tableau is taking my 2.8 and adding unnecessary decimal places that are in effect changing the number itself (my underlying data has this as 2.8, not 2.7999... as is displaying after the string conversion).

       

      The closest solution I could find on the forums was for someone who also needed a rounding function and this works but the problem is that I don't want to round my integers. In other words I want 2.8 to be 2.8 and 3 to be 3. If I round this field, 2.8 stays 2.8 instead of 2.7999... (which is good) but 3 becomes 3.0 (which doesn't work for my bigger workbook as I will be rounding to 3 decimals and don't want 28.000, i.e.).

       

      Any help would be appreciated but I'd also like to know why this is happening as it seems quite curious to me.

       

      Thank you in advance for your help!

        • 1. Re: Formatting Issue with a Decimal as a String
          Zach Leber

          Hi Rebecca, I've hit that problem several times and never figured it out, it's probably due to the order of the underlying calls behind the Tableau functions. In your case, if you're okay with 3 decimal places for non-integers, you could use something like the following formula to generate either 2.800 or 3.

           

          IIF(FIND([Max, Round, String], ".000") > 0, // check for all zeros after the decimal
          SPLIT([Max, Round, String],".",1), // if all decimals, just show the part left of the decimal
          [Max, Round, String]) // otherwise show everything

           

          Regards,

          Zach

          1 of 1 people found this helpful
          • 2. Re: Formatting Issue with a Decimal as a String
            Zach Leber

            This is better:

             

            IF MAX([Value]) - INT(MAX([Value])) != 0 // updated to handle negative values that round up

            THEN STR(ROUND(MAX([Value]), 3)) // round to 3 decimal places if non-integer

            ELSE STR(INT(MAX([Value]))) // otherwise convert int to string

            END

            1 of 1 people found this helpful
            • 3. Re: Formatting Issue with a Decimal as a String
              Rebecca LoSurdo

              Thank you Zach!

               

              I was going to do something similar but I stopped because I thought there had to be a reasonable explanation I was missing (and therefore a better way than just coding integers and floats differently). So thank you twice - for confirming I'm not crazy and that it is weird and for the very simple, effective code for it.

               

              Appreciate it!