9 Replies Latest reply on Feb 6, 2012 9:20 AM by James Pacey

# Count categories across two fields

Hi all, I'm building a bubble chart and I am looking for a calculation that will return the count of records in several categories but the categories span 2 dimension fields, category 1 and category 2.

For example:

 Category 1 Category 2 A A A B A B B C C C C C

Performing a count on Category 1 (or using its CNT on the x axis and placing it on level of detail) would return:

 CNT A 3 CNT B 1 CNT C 2

Doing the same to Category 2 would return:

 CNT A 1 CNT B 2 CNT C 3

I want my calculated field to effectively sum the two and return this when placed on the bubble chart:

 CNT A 4 CNT B 3 CNT C 5

I imagine it's fairly straight-forward but I can't wrap my head around it!

Thanks everyone

• ###### 1. Re: Count categories across two fields

This is a klutzy way to do it, but if your example is as simple as the data above...

Three calc fields each one like this:

zn(if [Category 1]="A" then 1 end) + zn(if [Category 2]="A" then 1 end)

• ###### 2. Re: Count categories across two fields

Amazing coincidence - I'm trying to figure out the exact same!   If you do figure it out on your own, please post the solution.  I'll keep trying as well.

• ###### 3. Re: Count categories across two fields

Did you see my answer Lee?

• ###### 4. Re: Count categories across two fields

Thanks for the quick response.

Unfortunately I need to do it within a single calculated field that I can then drop onto my bubble chart and the number of categories is generally going to be well into double figures.

• ###### 5. Re: Count categories across two fields

So I think you're going to have to either have to reshape your original data or use custom SQL in the connection:

SELECT [Clipboard_20120203T133650#tab].[Category 1] AS [Category 1]

FROM [Clipboard_20120203T133650#tab]

Union all

select [Clipboard_20120203T133650#tab].[Category 2] AS [Category 1]

FROM [Clipboard_20120203T133650#tab]

If you need to keep the original category data, you can use:

SELECT [Clipboard_20120203T133650#tab].[Category 1] AS [Category 1], 'Category 1' as [Orig]

FROM [Clipboard_20120203T133650#tab]

Union all

select [Clipboard_20120203T133650#tab].[Category 2] AS [Category 1], 'Category 2' as [Orig]

FROM [Clipboard_20120203T133650#tab]

1 of 1 people found this helpful
• ###### 6. Re: Count categories across two fields

Ahah. I hadn't thought about reshaping the data like that - unfortunately I don't speak SQL! The data is from a satisfaction survey so the category dimensions are shown against a score measure on the bubble chart. That means the reshaping would need to look like this:

ScoreA      Cat 1A     Cat 2A

ScoreB     Cat 1B     Cat 2B

reshaped to:

ScoreA      Cat 1A

ScoreB     Cat 1B

ScoreA     Cat 2A

ScoreB     Cat 2B

Any tips on how to achieve this would be very much appreciated.

Thanks

James

• ###### 7. Re: Count categories across two fields

Should be as simple as below, but obviously with the right names - pick apart what Tableau starts you with.

In essence this is saying - top part - go through every row and get category 1, the score, and label each of these rows with the text 'Category 1', then add on to the bottom (union all) the same rows but with the category 2 details.

SELECT [Source].[Category 1] AS [Category 1], 'Category 1' as [Orig], [Source].[Score] AS [Score]

FROM [Source]

Union all

select [Source].[Category 2] AS [Category 1], 'Category 2' as [Orig], [Source].[Score] AS [Score]

FROM [Source]

• ###### 8. Re: Count categories across two fields

Be aware of course that a simple sum([score]) will now be double the original data.

• ###### 9. Re: Count categories across two fields

Thanks, I'll give it a whirl.