5 Replies Latest reply on May 18, 2016 1:04 AM by Charlie Archer

# LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Hi All,

I'm just getting started with Tableau 9 and I have a visualization I need to show in a bar chart and/or a straight count.

Each person answers a questionnaire (Category/SubCategory) and is matched with other people that have the same answers for Category/SubCategory.

Here's what I need help with:

• I need to count the number of categories a person matches on with another person(s) - they can't have more than 4 categories, but they can have less. At this point, I don't need to know their subcategories, just whether or not they match on categories. I'm assuming this would be a LOD fixed on categories?
• I need to count the number of people who match with someone else on 4 categories
• I need to count the number of people who match with someone else on 3 categories
• "" 2 categories
• "" 1 category

I would also need help with the corresponding percentages for the number of people who match with each of the 4, 3, 2, 1 groups (out of the total population).

I think where I am getting tripped up is that I have a right and a left person and I'm not sure how to count those, and then aggregate them again because I have a right and a left person.  I tried to do a {FIXED [Category]: COUNTD([MATCHING PAIRS])}, but then I think I need to somehow create buckets for the 4, 3, 2, 1, and then aggregate again something like {FIXED [Buckets]: ...}?

It would be so helpful if someone could help me and then comment the calculated fields so I can understand and learn.

Thank you.

• ###### 1. Re: LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Hi Marie,

I have had a look through this and think that i may have created the required view by using the following LOD calc:

{FIXED [Left Person], [Right Person]: COUNTD([Category])}

This calculation is stating that, for each matching pair, i want to calculate the number of categories matched.

To create the buckets that you mentioned, i have simply converted the LOD calculation to Discrete (right click on the measure in the data pane and select 'Convert to discrete'.

Now when i add this new, discrete, LOD calc to rows, and COUNTD(Left Person) to columns i have a bar chart showing the number of (unique) people that have matched with someone else across 1, 2, 3, and 4 categories.

Hope this makes sense (and answers your question!).

Thanks,
Charlie

• ###### 2. Re: LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Hi, This is helpful. I'm wondering if you could help me with something else now?

I built the view, but it is showing like this:

If a person has 4 matches, it shows 4 marks on the bar on the 4 matches bar. I want that person to show as 1 mark on the bar.  Is there a way to group them again?

I tried a nested LoD, but It's not working.

Thank you again.

• ###### 3. Re: LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Hi there,

I'm not sure if i understand - maybe it would be useful to show the stage that i'm at.

I have a bar chart showing the unique number of people with 1, 2, 3 and 4 matches. When a single 'left' person matches 1 category with multiple 'right' people, they are only counted once in this chart.

Was this what you meant by the above?

Thanks,
Charlie

• ###### 4. Re: LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Workbook didn't properly attach to last message...

• ###### 5. Re: LoD - Aggregate Pairs of People and Aggregate those Pairs by # of Categories Paired on - TWBX

Unfortunately i'm not able to send the workbook - Tableau gives me an error.

Screen shots below:

Thanks,
Charlie