8 Replies Latest reply on May 10, 2018 5:50 PM by shahana.syed.0

    Sum of all Columns

    Jacob Powell

      Hi All,

       

      I have a dataset which looks like:

       

      ID
      Thing1
      Thing2Thing3Thing4Thing5Thing6
      123411
      5678111
      9012212

       

      I would like to create a totals column which would total all the columns.  In the real dataset, there are ~100 columns, so writing a calculation summing all the values is probably not doable.  Would like it to look like this:

       

      ID
      Thing1thing2thing3thing4thing5thing6TotalThings
      123411

      2

      56781113
      90122125

       

       

      Any ideas? I am sure this is a silly question, but next to T. Desktop and prep. Thanks!

       

      J

        • 1. Re: Sum of all Columns
          ShivaRam Chennapragada

          You could do this by dragging Totals from Analytics pane, selecting Column Totals. Here's the link on how to do it,

          Show Totals in a Visualization

           

          Hope this helps.

           

           

          Thanks,

          Shiva.

          1 of 1 people found this helpful
          • 2. Re: Sum of all Columns
            Jacob Powell

            Shiva,

             

            That's great!  One of those "duh" moments... except for, I must be still doing something wrong because it is not calculating the totals.

             

            When I started the cleanup, I had a set which looked like this:

            ID
            Things
            1234thing1
            1234thing2
            1234thing5
            5678thing2
            5678thing6
            9012thing3
            9012thing6
            9012thing4

             

            I converted this to a cross tab version, which gave me each variation of "thing" across the top.  Things are strings.  I created a group of all the [Things].  I then added a calculated column which is:

             

            IF ISNULL([Things] THEN 0 ELSE 1 END

             

            I added this as a column and set to Attribute.  This has given me a binary, 0 or 1 for each thing as features with key ID.  So, I want to create a column (explained in original post) which sums the total [Things] for [ID].  In theory, the totals column should do that, but it does not like my method, because it too is simply binary, 0 or 1.  Thanks and sorry for the confusion.

            j

            • 3. Re: Sum of all Columns
              Jim Dehner

              Hi Jacob -

              if I understand correctly you have pivoted the data and you created a counter and set to Attribute?

               

              and it returned a 1 when you placed it on the chart and totaled it -- try just plain count(things)  and see if that gets you what you want

               

              Jim

              • 4. Re: Sum of all Columns
                ShivaRam Chennapragada

                Could you attach a packaged workbook? It is lot easier to look at it and figure out what's going on.

                • 5. Re: Sum of all Columns
                  Jim Dehner

                  sorry if you just count the Things it could double count

                   

                  instead you and window_count the attribute you place in for Things

                   

                  window_count(count([Calculation2]))

                   

                  Jim

                  • 6. Re: Sum of all Columns
                    shahana.syed.0

                    Hi,

                    Select all the columns except the ID column and pivot.

                    Now bring ID, Pivot field names to the view and take the ROW TOTALS.

                    Attaching the workbook and the result!

                    • 7. Re: Sum of all Columns
                      Jacob Powell

                      Jim,

                       

                      When I pivoted the data, the attributes are now features and the values are either null or 'Abc'.  I then created a calculation which would return a 1 if the cell was not null.  added that to the table in columns.  If Thing1 existed for ID 1234, then now there is a 1 in the Thing1 column.  In theory, if I total or count all things for ID1234, it should sum the total number of 1's (thing1, thing3, etc.).  Instead of the sum total, it is returning a 1. 

                       

                      This is in a standalone pc, so I will try and pull down a copy of the workbook to post.  I'm sure there is something fundamentally simple which I am not doing.  Basically, I am looking a groups degrees.  I could do a simple count of how many degrees does someone have, but what I want is the model to be able to look at the various types of degrees themselves; which is why I am pivoting the data, and converting the presence of a certain degree for a person as a binary (do they have this type of degree or not?).  The last step is to create a column which totals the 1s (degrees present) for each person.  Essentially, trying to train a model to see if there is any signal in the various types of degrees for a person.  Easy to do in Excel, but I am trying to move to Tableau.  Thanks!

                      • 8. Re: Sum of all Columns
                        shahana.syed.0

                        Pivoting in Tableau helps when the data is in wide format. If you have NULL values, try to replace them with an integer value that you can recognize. Please post the workbook with raw data to be able to help.