4 Replies Latest reply on Mar 14, 2013 2:20 PM by Andrea McLain

    Is there a way to count a value throughout multiple columns?

    Andrea McLain

      Hi, I am a beginner in Tableau and working on my first project.  In my data set, I have 7 columns for referral reasons, since each record could contain up to 7.  Is there a way to get a count throughout all 7 columns for each individual referral reason in a Tableau visual?  I do not want to join the 7 columns into 1 in the custom SQL query because I cannot have any duplicate records as this would majorly throw off the counts in all other fields.

        • 1. Re: Is there a way to count a value throughout multiple columns?
          Joshua Milligan

          Andrea,

           

          It's a little hard to know if this is a correct answer without seeing the data, but my guess is that you will want to create a calculated field (right click in the data window under Dimensions or Measures and select "Create Calculated Field").  Give it a name like "Referral Count" and the code will look something like:

           

          Referral_Col_1 +  Referral_Col_2 + Referral_Col_3 + Referral_Col_4 + Referral_Col_5 +Referral_Col_6 + Referral_Col_7

           

           

          If that doesn't seem to work, please post a packaged workbook or sample data (with dummy values if sensitivity is an issue) and I'll be happy to take a look.

           

          Regards,

          Josua

          • 2. Re: Is there a way to count a value throughout multiple columns?
            Andrea McLain

            Joshua,

             

            Thanks for your reply!  I created the calculated field as you described, and this condensed the 7 columns to 1 by creating new values based on the combination of referral reasons within a row, so this will not work.  I created and attached attached a small sample spreadsheet so you can see what the data is like.  In the spreadsheet, I made up 10 unique codes to represent 10 different referral reasons.  I cannot condense the 7 referral reason columns outside of Tableau since that would duplicate the "RecordNumber" up to 7 times per record, and then all other fields, such as  "Color" in the sample spreadsheet, will have distorted inaccurate counts from the duplications.  The ultimate goal is to have a Tableau visual that lists the 10 referral reasons in the rows or columns, and then displays the count of each in the middle of the pane, but I can't mess up the rest of the data to get this.  I'm not sure if what I'm looking for is possible, but thank your help and your time.

             

            Sincerely,

            Andrea    

            • 3. Re: Is there a way to count a value throughout multiple columns?
              Joshua Milligan

              Andrea,

               

              It sounds as though you may need to re-shape your data.  Please refer to this thread for some details:  http://community.tableau.com/message/202539

               

              Regards,

              Joshua

              1 of 1 people found this helpful
              • 4. Re: Is there a way to count a value throughout multiple columns?
                Andrea McLain

                Joshua,

                 

                That is a pretty awesome tool.  I set it up successfully and will for sure be using it, but in this case, I cannot combine the 7 columns in Excel, or anything prior to Tableau, because of the duplicates that it creates which throws off the data in all of the other columns.  Due to the duplicate problem, I am looking into setting up a second data connection in the same workbook to pull out the 7 referral columns as 1 directly from SQL (I'm trying to stick to SQL connections since this data is loaded into SQL every day and can then refresh in Tableau).  Although this second connection will of course have duplicate data in all other columns, I will still have the original data to work from and will use this second connection only on a graphic for referral reasons.  This will likely be problematic when trying to set global filters in a dashboard, but it's a start.  Please let me know if you have any additional suggestions.  Thank you much for your assisstance!

                 

                Andrea