11 Replies Latest reply on Feb 22, 2018 6:44 AM by Simon Runc

# Customers Ordering only One Product every time

Hi There,

Can you please help me in identifying the number of customers who have ordered only one type of product throughout the years. (attached Workbook) .They should have not ever ordered a different type of product.

For eg. I want to find out number of customers who have ordered only Documents&Brochures every time they ordered. A relative filter on time period would be helpful.

I tried using the LOD :

1) { FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): COUNTD([Product Name])}  as a row against each customer id

2) filter : { FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): COUNTD([Product Name])=1}

but this is not giving me correct results. It is just hiding the products a customer has purchased and giving only random value.

Ideally I think it would have been correct if the Filter LOD calculation would've been like

{ FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): sum(COUNTD([Product Name])=1)=1}

but tableau does not allow aggregation of already aggregated values (sum(COUNTD([Product Name]))

Veekay

• ###### 1. Re: Customers Ordering only One Product every time

Hi, Varun

Your first calculation is correct, while 2nd one, you can do either [1st calculation] = 1 or

{ FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): COUNTD([Product Name])} =1

Hope this helps

ZZ

• ###### 2. Re: Customers Ordering only One Product every time

hi Varun,

So I think this formula should do the trick

[Customers Only Buying One Product Per Year]

IF {FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): COUNTD([Product Name])} = 1

THEN [Customer ID (Client)]

END

So this only brings back the Customer IDs (each Year) who only bought 1 product (with no ELSE anyone who bought more than one is NULL), and as NULLs don't get counted in COUNTD, we can bring this field in with the COUNTD aggregation.

Hope that's what you were after and makes sense.

• ###### 3. Re: Customers Ordering only One Product every time

Hi Simon,

Thanks for the response

Unfortunately it does not work as I was wanted to know only those customers who buy for eg. Business cards all the time and nothing else.

In your solution If I add a filter for a particular and then go on to check how many bought business cards it shows me the figure but also includes customers who bought business cards and also another product. I just want to have those customers who have ordered only business cards (please see the added sheets)

Apologies if I did not describe the question clear in first place, what I meant was customers ordering only one type of product and not customers ordering only one product

Thanks

• ###### 4. Re: Customers Ordering only One Product every time

Hi Zhouyi,

Thanks for the response,

the calculation filter still gives me people who have ordered more than one type pf products even if the filter says True

Regards

Varun

• ###### 5. Re: Customers Ordering only One Product every time

So we can account for that, if we make the following change to the formula

IF {FIXED [Customer ID (Client)], [Product Name], YEAR([Order Date (Completed)]): COUNTD([Product Name])} = 1

THEN [Customer ID (Client)]

END

Although if it's just a count of customers you want for a selected product, we can just do this with regular filtering and aggregation (see "Simon Alternative" tab).

Hope that helps

1 of 1 people found this helpful
• ###### 6. Re: Customers Ordering only One Product every time

Hi, Varun

Can you show me an example? e.g which custome id?

To me it looks correct.

ZZ

• ###### 7. Re: Customers Ordering only One Product every time

And if you talking all the years only for one product, then remove the year part in the calculation as shown below

{ FIXED [Customer ID (Client)], YEAR([Order Date (Completed)]): COUNTD([Product Name])} =1

ZZ

2 of 2 people found this helpful
• ###### 8. Re: Customers Ordering only One Product every time

If I view the data (customer ids) behind the year 2016 in your alternative solution and pick up any random customer id (for eg. 1852), I can see that this customer has bought 2 different products in 2016 and three different products in total. Because the sheet had a filter of "Product Name" on it was only picking up customers who bought Business cards but not the ones who only bought business cards.

Thanks

Varun

• ###### 9. Re: Customers Ordering only One Product every time

Hi Zhouyi ,

I removed the year from the LOD and it gave me correct result. Thanks this works for me.

Instead of adding year in the LOD, If I add that year as relative filter (for eg. last three years) , hope fully I shall be able to see a trend of a particular customer (based on Revenue) and how much revenue has this customer given over a period of years because he is buying only one type of product? Correct?

Regards

Varun

• ###### 10. Re: Customers Ordering only One Product every time

customer id 1942 in your screenshot

• ###### 11. Re: Customers Ordering only One Product every time

Cool...thought that might be the case, so yes if you want "only bought this and nothing else" then LoDs are the way to go.