5 Replies Latest reply on Apr 13, 2017 1:24 AM by Abhiroop K

    Comma separated item as bar chart column

    Abhiroop K

      Hi,

       

           I am trying the create a bar chart using a tabular information below. Can someone help me create the below bar chart wherein i want to show the count of ID's for each of distinct functionality and source?

       

         

      IDFunctionalitySource
      318986A,B,F,D,EP,Q,S,R
      101115D,EQ,S,U
      111324F,GS,T,V
      130784G,B,F,AQ,V,R
      463342B,E,A,FT,U,V
      533594C,GR,T,U
      137749E,F,B,GQ,S,V,T,P
      127203D,BS,V,P
      484620E,G,AT,S,R
      205283B,E,AV,Q,P
      303658F,GS,R,Q
      606127G,B,F,AT,U,V
      324383B,E,A,FR,T,U,P,V
      705256C,GQ,S,V
      645106E,F,B,GT,S,R
      783298E,G,AV,Q,P
      189077B,E,AQ,S,V
      764552F,GT,U,V
      738719E,F,B,GQ,S,V
      767351E,G,AT,S,R

       

      Thanks,

      Abhi

        • 1. Re: Comma separated item as bar chart column
          Abhiroop K

          Anyone having clue for the stated problem?

           

          Abhi

          • 2. Re: Comma separated item as bar chart column
            Brad Shuel

            Hi Abhiroop,

             

            After connecting to the data, we can create calculations to emulate the bar graph you are trying to achieve.

             

            1. Create a series of calculated fields similar to the following:

             

            A Count

            Count(If CONTAINS([Functionality],"A") then 1 END)

             

            B Count

            Count(If CONTAINS([Functionality],"B") then 1 END)

             

            etc. for each letter.

             

            2. Create the view.

             

            a. Drag Measure Names to Columns and Measure Values to Rows.

            b. Drag another copy of Measure Values to Label.

            c. Drag the unwanted measure off of the Measure Values card.

             

            I believe this will replicate what is happening in the excel file.

            • 3. Re: Comma separated item as bar chart column
              Deepak Rai

              Hi Abhi,

              Check The screenshot and the attached. I did it without any Table Calculation. I used the split and pivot. The steps are:

              • Split both your Functionality and Source using , as Delimiter. It would split into 5 Columns
              • Now go to your sheet . Click view data  under datasource.
              • Copy data and paste it making it another datasource. Let us say it Datasource1.
              • Pivot Functionality Splits. you have 5. Rename Pivoted data as Functionalty and Fvalue.
              • go to your Sheet view again and Click Datasource1 again now. Copy view data and again paste it making another data source. THIS is Final Data source. Let's say it the Datasource2.
              • Again go to edit Datasource2 and this time Pivot your Source splits. Rename them as Sources and Svalue
              • Now your Datasource2 is Ready for analysis.
              • Since you pivoted 5 Splits, data gets inflated.
              • Divide the Count of Functionality and Sources by 5 to adjust it.
              • Drag adjusted values to Rows and Fvalue and Svalue to Columns in two sheets to get this;

              Hope it Helps!!!

              Let me know

              Thanks

              Deepak

              1 of 1 people found this helpful
              • 4. Re: Comma separated item as bar chart column
                Abhiroop K

                Hi Brad,

                 

                Thanks for the reply, I thought of similar steps but there are some drawbacks.

                1. In my actual data-set, there are 5 fields with such comma separated items and the distinct count of such items goes beyond 120 which is too much to create the calculated field.

                2. Another problem is that i don't have the name of these items readily available in a single field here so these items wouldn't appear in measure names.

                 

                Thanks,

                Abhi

                • 5. Re: Comma separated item as bar chart column
                  Abhiroop K

                  Hi Deepak,

                   

                       Thanks for your post!! I went through your post and it appears that its pretty much same as my expected graphs. However, I wondered that my 20 rows of data and 2 columns with max 5 comma separated items are ending up with 503 row at the end so what will happen if i do similar exercise with my actual data-set(having 20000 rows and 5 columns with max 10 comma separated items in a row).

                   

                       I noticed that you are making new data-set as many number of times as the column count with comma separated values is; so i can create one graph for each column in separate tableau workbook and avoid making new data-set for single workbook.

                      

                       I searched for similar topic in community and got to a very close thread where this problem is solved via cross join technique but i am unable to open that workbook.

                       Comma Separated value within a Column

                  Thanks,

                  Abhi