3 Replies Latest reply on Nov 27, 2017 4:32 PM by Peter Fakan

    Different data types in same column.

    Scott Schmeling

      Good Morning,

       

      I am working on a data set and worksheet that have different data types in the same column.  For example:

       

      Column A: KPI header

      Column B: KPI Measure

       

      The thing is that the measures are different types of data.  We have $ for some and % for others.  I would like to be able to change the data type dependent on the header and show them all in a dataview. 

       

      Here is a rough idea of what I'm looking for:

       

       

      Header 1Header 2
      Sales$500,000
      New Clients25
      Client Retention87.63%

       

      Any suggestions on how to do this.  We have tried the following:

       

      Turned the numbers into String and adding a prefix and a suffix, the issue with this is that for some reason Tableau takes the % and adds 8 decimal places and adds a 5 to the end.  The raw data from SQL server only carries out to 4 decimal places as verified on the server itself. 

       

      SQL Server number might be 0.8763

      Tableau turns it into:  87.63000005%

       

      Any suggestions would be appreciated.

       

      Thanks,

        • 1. Re: Different data types in same column.
          Yuriy Fal

          Hi Andrew,

           

          You may want to pivot each KPI to it's own column

          and apply different formatting according to your rules.

          Then use Measure Names / Measure Values on a view.

           

          Yours,

          Yuri

          • 2. Re: Different data types in same column.
            Scott Schmeling

            Thanks Yuri, but the requirements are clear that I have it all in one column.

             

            I figured that out, using a formula that converts all the string. Now have an issue getting commas where I need them in the numbers but that is another post. 

             

            Formula I ended up using it:

             

            If attr([Kpi Nm]) = "Policy Retention Better-than-State Average"

            Then str(round(sum([Actual]) * 100, 2)) + "%"

            ElseIf attr([Kpi Nm]) = "Policy Retention Improvement (or > 90%)**"

            Then str(round(sum([Actual]) * 100, 2)) + "%"

            Elseif attr([Kpi Nm]) = "Premium Growth"

            Then str(round(sum([Actual]) *100, 2)) + "%"

            Elseif attr([Kpi Nm]) = "PIF Growth"

            Then str(round(sum([Actual]), 2))

            Elseif attr([Kpi Nm]) = "Product Density"

            Then "NA"

            else

               STR(ROUND(LOOKUP(sum([Actual]),0),0))

            End

            1 of 1 people found this helpful
            • 3. Re: Different data types in same column.
              Peter Fakan

              Hi Andrew,

               

              There is a 'hidden' place where you can edit the format of numbers - try using the drop-down caret on the measure, and set the format from there.

               

               

              HTH

               

              Peter