11 Replies Latest reply on Oct 23, 2013 4:01 AM by Ville Tyrväinen

# Counting items across multiple columns

I have multiple columns with the set of items (1-20) and would like to get a count of unique items across these columns.

I am attaching a sample .twbx. I want to know the count of 1s, 2s, .... 20s across columns C and D (GroupA and GroupB).

I am able to do this for one column (e.g. GroupA) by moving it to Dimensions. How can I get a count across two columns?

Also, I would also like to see the count by Gender and Age. Is reshaping the only way to do achieve this?

• ###### 1. Re: Counting items across multiple columns

Hi Anuja,

If you select GroupA(copy) and GroupB(copy), right click and combine fields and use this instead of GroupA does that give you what you're after?

I'm struggling a bit to understand what you're counting... Would you be able to explain a bit more?

Thanks,
Kevin

• ###### 2. Re: Counting items across multiple columns

Thanks for writing back Kevin but that won't work, I tried that.

Let me try to explain this better. I have two columns i.e. Group A and Group B. What I need is the total/combined count of items in these two columns.

Group A

Item A

Item A

Item B

Item B

Item C

Group B

Item A

Item B
Item B

Item C

The final output combining the two columns should be as follows:

Item A - 3

Item B - 4

Item C - 2

When I "combine fields" I get something like "Item A, ItemB" as in it starts combining items row wise. What I need is a list of all unique items with the total count across the two columns.

Hope this is clear.

• ###### 3. Re: Re: Counting items across multiple columns

Hi Anuja,

Considering our data is organized in two columns: Group and Item, as presented below:

Group,Item

Group A,Item A

Group A,Item A

Group A,Item B

Group A,Item B

Group A,Item C

Group B,Item A

Group B,Item B

Group B,Item B

Group B,Item C

It is very straightforward the process of counting the number of items. Open the data set in tableau, drag dimension Item to Rows and Number of Records to Text.

The results is in the attached workbook.

Best,

Ramon

• ###### 4. Re: Re: Counting items across multiple columns

Thanks Ramon.

Data is organized in two columns but the two columns are GroupA and GroupB (the columns are not Group and Item) and the values in the column are exactly like how I've pasted above.

• ###### 5. Re: Re: Re: Counting items across multiple columns

Hi Anuja,

I think if you change the data sructure to below format using Data Connection-> Custom SQL.

 Group Item Group A Item A Group A Item B Group A Item B Group A Item C Group B Item A Group B Item A Group B Item B Group B Item B Group B Item C

Example:

```SELECT [Sheet2\$].[AGE] AS [AGE],
[Sheet2\$].[Gender] AS [Gender],
[Sheet2\$].[GROUPA] AS [GROUP]
FROM [Sheet2\$]

union

SELECT [Sheet2\$].[AGE] AS [AGE],
[Sheet2\$].[Gender] AS [Gender],
[Sheet2\$].[GROUPB] AS [GROUP]
FROM [Sheet2\$]
```

Once the data shape got ready then just drag Group into Rows and  No Of Records to Text.

• ###### 6. Re: Re: Re: Counting items across multiple columns

Thanks Indumon. I was able to recreate what you have done. But then I noticed the results are wrong.

For e.g. if you count the number of "1" in Group A alone (prior to changing the data structure), it is 125. Now, in the reshaped version I get a total count of 7 for "1" (Group A and Group put together).

Any thoughts on why that happens? I am not too familiar with SQL.

• ###### 7. Re: Counting items across multiple columns

You are looking these values?

• ###### 8. Re: Re: Re: Counting items across multiple columns

Yes! Ville, exactly "right". How did you do this?

I would eventually like to use the "Group" column and the "Total" column to create a horizontal bar chart showing a count % which will be a column %.

• ###### 9. Re: Re: Re: Re: Counting items across multiple columns

I just added one excel sheet with Group column and then duplicated original sheet and edited relationships. After that I made couple calculated fields. It's only a quick trick and needs to be modified. Maybe someone else can guide you because i'm not familiar with sql. I just do things

• ###### 10. Re: Re: Re: Re: Re: Counting items across multiple columns

If you add couple columns more:

• ###### 11. Re: Counting items across multiple columns

Anuja S Krishnan wrote:

Thanks Indumon. I was able to recreate what you have done. But then I noticed the results are wrong.

For e.g. if you count the number of "1" in Group A alone (prior to changing the data structure), it is 125. Now, in the reshaped version I get a total count of 7 for "1" (Group A and Group put together).

Any thoughts on why that happens? I am not too familiar with SQL.

You have 6 age groups and men and women, so you have 12 "groups" from where Tableau is calculating " How many of these groups have atleast one record". An as you can see "1" has values in 7 different age/*** groups.