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

Sum of all Columns

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

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

Hope this helps.

Thanks,

Shiva.

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

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

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

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

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

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

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

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.