6 Replies Latest reply on Feb 22, 2016 12:29 PM by Maxime Kehon

    Count each instance of a string in a single Dimension

    matthew.sullivan.2

      Hello Folks,

       

      Long time lurker, user, first time poster. It's not often I can't find my question already answered, this community is awesome and I really appreciate the time spent by all.

       

      Unfortunately, I can't provide the data I'm working with but I can provide a sample data. I'd like a running count of the number of instances of a string within a single Dimension for use in a Tree Map.


      The first column is for unique ticket numbers. The second column in my sample .csv contains one or many instances of types of drinks (best I could come up with for samples), I'd like to count each instance of the drink.

       

      sample.JPG

       

      I attempted to create a calculation IF CONTAINS ([Drink Categories],"Pepsi") THEN "Pepsi" .... for each category of drink, this did provide me a count but only counted the first string in each field. For example, field B6, could only count Diet Coke, but would not continue to count the rest. I'd like to use these counts in a tree map which I'll ultimately use in a Dashboard to correlate Drink Categories and the unique tickets which requested the drink. I thought about splitting the cell on the data source, however, the actual report as so many columns that I was hoping to avoid more columns and it looks like the split is limited to 10.

       

      Thanks in advance,

      Matt

        • 1. Re: Count each instance of a string in a single Dimension
          Maxime Kehon

          Hi Matthew,

           

          I don't know if it is the best solution but you can try the following calculation and at least it works :

           

          IF     FINDNTH([Request Categories],";",1) = 0 THEN 1
          ELSEIF FINDNTH([Request Categories],";",2) = 0 THEN 2
          ELSEIF FINDNTH([Request Categories],";",3) = 0 THEN 3
          ELSEIF FINDNTH([Request Categories],";",4) = 0 THEN 4
          ELSEIF FINDNTH([Request Categories],";",5) = 0 THEN 5
          ELSEIF FINDNTH([Request Categories],";",6) = 0 THEN 5
          ELSEIF FINDNTH([Request Categories],";",7) = 0 THEN 6
          ELSEIF FINDNTH([Request Categories],";",8) = 0 THEN 8
          ELSEIF FINDNTH([Request Categories],";",9) = 0 THEN 9
          END
          

           

          Of course you can keep adding lines and expand the count to 99 or 999, but if you have to do that I will suggest you to create the formula using an excel sheet .

           

           

          Cheers,

          Maxime

          1 of 1 people found this helpful
          • 2. Re: Count each instance of a string in a single Dimension
            matthew.sullivan.2

            Hello Maxime,

             

            Thank you. Interesting approach (saving this approach for a different viz). However, for this scenario, it won't give me the unique count for each type of drink. I'd like to be able to break apart each on of the types of drinks and how many times they were requested.

             

            sample.JPG

            For example,

            Pepsi = 3

            Coke = 1

            Mountain Dew = 3

            etc...

             

            This way I can produce a tree map with count of drink types. Using the calculation you provided, it counts the number of drink per ticket and give me the ticket with the most of amount of drink request. Tree map with the above calc.

             

            test_tree.JPG

             

            Thanks alot for the assistance.

            • 3. Re: Count each instance of a string in a single Dimension
              Maxime Kehon

              You have to create a calculation for each of the category.

               

              Something like:

               

              Count of Pepsi Appearance:

              IF          FINDNTH([Request Categories],"Pepsi",1) = 0 THEN 1

              ELSEIF FINDNTH([Request Categories],"Pepsi",2) = 0 THEN 2

              ELSEIF FINDNTH([Request Categories],"Pepsi",3) = 0 THEN 3

              ECT...

              END

               

              Count of Coca Appearance:

              IF          FINDNTH([Request Categories],"Coca",1) = 0 THEN 1

              ELSEIF FINDNTH([Request Categories],"Coca",2) = 0 THEN 2

              ELSEIF FINDNTH([Request Categories],"Coca",3) = 0 THEN 3

              ECT...

              END

               

              But you can not create one calculation which gives you all the info in one column. Maybe I am not understanding what you want to do, if it is the case add one more column "Expected Value" to your excel file and send it over.

               

              Thanks,

              Maxime

              • 4. Re: Count each instance of a string in a single Dimension
                Ben Page

                Hi Matthew,

                 

                I think it would be very beneficial to reshape your data. Something like this would work well for you:

                reshaped data.PNG

                This will maintain your relationship between ticket number and drink categories. It will clearly expand the size of your data source, but it won't require the addition of any columns. Finally, it would easily allow you to produce the treemap you're looking for without any crazy workarounds. I've attached a workbook.

                 

                Thanks,

                Ben

                2 of 2 people found this helpful
                • 5. Re: Count each instance of a string in a single Dimension
                  matthew.sullivan.2

                  Thanks alot Ben and Maxime.

                   

                  Re-shaping the data is also a different approach, however, I can't control the output of the report from the data source. I receive the report in a weekly .csv and would need to manually re-shape each output weekly, unless there's a script I could make within Tableau? I'm not too strong on the ingestion script. I'll need to look around a bit.

                  • 6. Re: Count each instance of a string in a single Dimension
                    Maxime Kehon

                    Ben's proposal is actually the correct approach from a data structure. For this kind of exercise you should try to use a third part software to massage/re-shape (and automate the process) your data before importing them to Tableau. You should take a look at Alteryx.

                     

                    Thanks,

                    Maxime