3 Replies Latest reply on Jun 27, 2016 10:22 AM by Dmitry Chirkov

    How to convert string to number using tableau calulation

    Kannan Athmanathan

      Hi All,

       

      I have created a bar chart and I need to show overall CRF Pages in the label. I created the calculated field as  "Overall CRF Pages - " + STR(SUM([Page Count])) to show the heading as shown in the below screen shot.

      ex1.JPG

      However I need the resulting value  after the overall sales text with a thousand separator ',' as shown in the below screen shot.

      ex2.png

      I created the calculated field as:

       

      IF LEN([Overall PageCount]) > 3 THEN

      "Overall CRF Pages - " + LEFT([Overall PageCount],2) + "," + RIGHT([Overall PageCount],3) + " Pages"

      END

       

      This works fine for the 5 values, however, its not working with more than 5 values.

       

      Here i attached the sample packaged workbook for your reference.

        • 1. Re: How to convert string to number using tableau calulation
          John Sobczak

          Have you tried the below?

           

          IF sum([Sales]) >= 1000 and sum([Sales])  < 10000 THEN
          "Overall Sales - " + LEFT(str(sum([Sales])),1) + "," + mid(str(sum([Sales])),2,3)

          elseif
          sum([Sales]) >= 10000 and sum([Sales]) < 100000 THEN
          "Overall Sales - " + LEFT(str(sum([Sales])),2) + "," + mid(str(sum([Sales])),3,3)

          elseif
          sum([Sales]) >= 100000 and sum([Sales])  < 1000000 THEN
          "Overall Sales - " + LEFT(str(sum([Sales])),3) + "," + mid(str(sum([Sales])),4,3)

          elseif
          sum([Sales]) >= 1000000 and sum([Sales])  < 10000000 THEN
          "Overall Sales - " + LEFT(str(sum([Sales])),1) + "," + mid(str(sum([Sales])),2,3) + "," + mid(str(sum([Sales])),5,3)

          END

          • 2. Re: How to convert string to number using tableau calulation
            Michael Hesser

            Hi Kannan--

             

            I think you might find your answer here:

            Number Formatting with Comma Separator inside Parameter Calculation

             

            Matt Lutton wrote this beauty and he took hammered it out the good-old fashioned way: analyzed each sum and applied the correct formatting. Hopefully your data won't exceed 100 Trillion.

             

            His code, which I'm sure you can tweak, is:

             

            ----------------------

            PARAMETER CALC

            CASE[Choose Measure]

            when "Sales" then

            IF len(str([Sum Sales]))<3 THEN

                "$" + str([Sum Sales])

            ELSEIF len(str([Sum Sales]))<=6 THEN

                "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-3)

                + "," + RIGHT(str([Sum Sales]), 3)

            ELSEIF len(str([Sum Sales]))<=9 THEN

                "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-6)

                + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                + RIGHT(str([Sum Sales]), 3)

            ELSEIF len(str([Sum Sales]))<=12 THEN

                "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-9)

                + "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

                + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                + RIGHT(str([Sum Sales]), 3)

            ELSEIF len(str([Sum Sales]))<=15 THEN

                "$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-12)

                + "," + MID(RIGHT(str([Sum Sales]), 12), 1, 3)

                + "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

                + "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + "," 

                + RIGHT(str([Sum Sales]), 3)

            ELSE

                "More than 100 Trillon Dollars"

            END

             

             

            when "Ratio" then str(round(sum(Profit)/([Sum Sales])*100, 2))+"%"

            end

            ------------------

             

            Good luck!

            • 3. Re: How to convert string to number using tableau calulation
              Dmitry Chirkov

              You just need to format the label itself.

              1. Put measure you want on the label
              2. Format it the way you need (separators, numbers after comma etc)
              3. Modify label's text - your 'Overall ..." text goes there.

               

              1 of 1 people found this helpful