11 Replies Latest reply on Dec 3, 2014 8:38 AM by Jonathan Drummey

# Data overlapping

Hello,

I am new to Tableau and cannot find the appropriate solution for quite some time.

The problem that I am solving - I need to calculate intersection/overlapping of particular attribute.

For example. For data

 ID Attribute 1 A 1 B 1 A 1 B 1 C 1 D 1 E 2 A 2 A 2 A 3 B 3 C 3 C

I need to calculate how many distinct IDs have Attribute A, B, C (easy part), then A&B, A&C, A&D, A&E, B&C, B&D  etc.

I can achieve this by using multiple sheets for each attribute. One for A, one for B, etc

Also, it maybe be possible to achieve transforming data. Neither of these two options are apealing. Thus solution in single sheet would be welcom if this is possible to do with tableau.

• ###### 1. Re: Data overlapping

Hi Rimantas

Just having a quick look...

This will solve the concatenation problem -

STR([ID])+[Attribute]

(need to make ID a string as its currently an integer)

i don't understand the A&B, A&C bit... could you explain a little further?

I'm assuming this is the 'easy bit'

Mark

• ###### 2. Re: Data overlapping

Hi Marc,

maybe if I provide the result expected, It will be clearer.

For the given input, the output should be smth like:

Attribute:Count

A: 2

B: 2

C:2

A&B: 1

A&C: 1

A&D:1

A&E: 1

B&C:2

B&D:1

B&E:1

etc.

By & I mean the set intersection. So in case of B&C - there are 2 IDs that have BOTH, attribute B AND attribute C.

Does this explain better what I want to achieve?

• ###### 3. Re: Data overlapping

Hi Rimantas

The first thing which comes to mind is this thread Creating the equivalent of a Venn diagram? which has an answer provided by Joe Mako.  I've used his solution here (Venn alternative using custom SQL).

There has been an idea created for Venn diagrams (http://community.tableau.com/ideas/1582) and in the comments there, Jonathan Drummey has shown a good work around (his workbook is here: http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/vennalternatives/customSQLbasicbar).

For a different method which may fit your purpose (depending upon how you want to display the results) have you looked at Bethany Lyon's fantastic Think Data Thursday video on Sets (at the bottom of this list:The specified item was not found.)?  It's really worth watching the whole lot but about half-way through she talks about creating sets for different attributes and showing how many times different attributes occur together.

BW

Juliette

1 of 1 people found this helpful
• ###### 4. Re: Data overlapping

Welcome to the forum, Rimantas!

I wonder if wanted output is something like mocked up result below:

• ###### 5. Re: Data overlapping

Hi Juliette,

thanks a lot. I think I will be able to work this out from the video. Though I am not sure, where to find the workbook (in video she says that it will be uploaded, just I don't know where).

Regarding Venn diagrams and workarounds. Yes, this is exactly what I am interested in. However, since I am using Mac OS, the custom sql solution is not really an option for me (apparently neither is the extract api, which also does not work with Mac OS).

Anyway, thanks for a useful reference to the video.

• ###### 6. Re: Data overlapping

Hi,

the expected outcome is not like that. Or at least not entirely. Because I am interested in intersection, not only presenting complete sets of Attributes, that particular ids have.

• ###### 7. Re: Data overlapping

Hi Rimantas

The workbook is available here:

Re: Think Data Thursday - November 20 - Let's talk about Sets Baby!

I don't think Joe's solution used custom SQL.  It's basically a calculation based on a CASE statement so that might work for you.

Juliette

• ###### 8. Re: Data overlapping

Thanks. I think I still struggle understanding expected output, but wonder if this is closer to it:

Ps. B & C is an alias for In

• ###### 9. Re: Data overlapping

Hi,

yes this is more like it.

• ###### 10. Re: Data overlapping

I hope the attached is close to a real solution.

• ###### 11. Re: Data overlapping

Also see this thread from Mark Jackson...