9 Replies Latest reply on Apr 15, 2017 9:01 AM by mortenbodaugaard.jrgensen

# Customer intersection

Hello vizards of oz,

I'm trying to list all the customers who made purchases in both a & b product sub-categories, like on the picture below.

These a & b are defined by me using a quick filter on product sub-category. How can I accomplish this? (on the explanation can you please elaborate on your thought process, please?)

Very appreciated,

Esther

• ###### 1. Re: Customer intersection

Anyone?

I'm guessing it involves a LOD expression...

• ###### 2. Re: Customer intersection

Give me 10 minutes

• ###### 3. Re: Customer intersection

Hi Esther

See attached - the biggest thing to remember with this example is that the filter for sub-category is in context.

1 of 1 people found this helpful
• ###### 4. Re: Customer intersection

Ok, I'm having some trouble processing the FilterToAmountOfCategory calculation all those FIXEDs...

I guess I understand it in a general way, but could you please go a little bit deeper in your reasoning? If that isn't too much to ask for...

Thanks immensely!

Esther

• ###### 5. Re: Customer intersection

Sure.

So I start by doing a simple calculation to find the unique number of orders per customer within each subcategory:

Then I modify my calculation so I get for each customer/category combination the number 1 if they have bought in the category - meaning that if I SUM this measure I will get the number of categories each customer has bought in.

I then aggregate this measure for each customer - meaning that I want to aggregate for each customer the amount of subcategories they made orders within.

Since I put my filter on sub-category into context, it is evaluated BEFORE my FIXED calculation (Tableau's Order of Operations )

Finally I just test whether the user have bought within the amount of sub-categories that are still in the filter context

Meaning that this calculation tests whether the amount of categories a user have bought within is equal to the amount of categories in the filter context.

You can use my workbook to check what users have bought within N categories in your filter.

1 of 1 people found this helpful
• ###### 6. Re: Customer intersection

Thank you so much for the thorough explanation.

Gosh, this is hard to me to get my head around.

There are some subtleties still troubling me but I guess that's just because I still don't have enough practice with LODs...

Will come back to your explanation many times, I presume.

Esther

• ###### 7. Re: Customer intersection

Try experimenting with my workbook - I have added the version with Calc 1 through 3 so you can play around with them.

Try adding / removing subcategory to the view and see how that affect the calculations. That should help you get a better idea of it.

You can always hit me with an e-mail which I believe can be found on my profile - otherwise my name is pretty unique and I should be easy enough to find on LinkedIn and our company webpage (with full contact details)

-Morten

2 of 2 people found this helpful
• ###### 8. Re: Customer intersection

Thanks for the tip and readiness in helping me out Very, very appreciated!

I was indeed playing around with the calculations and spotted a strange thing (or so I see it): Calc2 and Calc3 always show the same value, independently of what is in the view.

Calc2 - number of sub-categories each customer has bought in

Calc3 - same than calc2 but aggregated to the Customer level.

They show the same value than the COUNTD(sub-category).

What are the practical implications of these 2 LODs?

Thank you greatly,

Esther

• ###### 9. Re: Customer intersection

Calc 2 ensures that I do not count multiple purchases (Order IDs) within the same subcategory more than once.

Calc 3 merely adds up the different categories in which the purchases have been made for each customer.

1 of 1 people found this helpful