2 Replies Latest reply on May 18, 2012 6:38 AM by carol.bass

    Deriving the number of instances of text strings in a specfiic field of a data set.

      There is a very nice video for generating a pie chart with both a text label and a percent - thank you Joe!  I need to do the same thing but the data used to derive the percent needs to be based on the number of instances of specific text strings in my set of records. For example, I could have a set of 200 records with a field containing one of the following words: "green", "yellow", "red", "on-hold", etc. (e.g. 100 may contain "green", 40 "yellow", 3 "red", none may have "on-hold" and others will have various other standard values - or a null and I need to account for nulls as errors.)   I am currently doing this in Excel by maintaining a metrics data layer which does the count and generating charts from that but was hoping I could dispense with that layer with Tableau and work from the raw data.  Can anyone help with how to do this? Thanks!

        • 1. Re: Deriving the number of instances of text strings in a specfiic field of a data set.
          Dimitri.B

          I am not sure if this would do the trick, but you can use the formula like the one below to pick items of interest and then calculate % of total via quick table calculation (see attached).

          I used sample data and looked for brand names in product names instead of your text. It can be easily adapted for any data.

           

          IF CONTAINS([Product Name],'Crayola') THEN 'Crayola'

          ELSEIF CONTAINS([Product Name],'DAX') THEN 'DAX'

          ELSEIF CONTAINS([Product Name],'Epson') THEN 'Epson'

          ELSEIF ISNULL([Product Name]) THEN 'error'

          ELSE 'other'

          END

          1 of 1 people found this helpful
          • 2. Re: Deriving the number of instances of text strings in a specfiic field of a data set.

            Thanks.  I'm learning the tool, to determine if it take care of our PPM (project and portfolio management) needs.

             

            There are more complex cases where we need a count and percent of all data elements (again, text strings)in a field on a monthly basis, and the results need to be trended by month, quarter and year by string in data table and chart form (likely histogram, with trend lines, but would love to get flashier...)  The text strings in this field can vary by month (not an infinite variety of strings but the list of strings can be added to at any time, and not all which existed last month will appear in a future month.) The process must do all this automatically, so it must identify the strings on it's own.


            This data is typical of project portfolio management in a large organization: not all departments have active projects each month, departments which have never made a request before can make one at any time and new departments are formed periodically. (Have to cope with departments merging too, but let's put that to the side right now.)


            I'm looking to not make production generation of these metrics and charts a full-time job and to not worrying that we miss a new "string/department."


            We are trying to make a decision as to whether or not Tableau is the right tool for this and had been hoping to find examples of Tableau being used for PPM but have not seen any, so really do appreciate your help!

             

            I think it's probably more appropriate for me to open a separate issue about this and will do so.

            Thanks so much!