Identify buyers taking products only from one seller

[Fiorrie]

Hi Guys

I want to count number of buyers that are buying only from one seller, without any specific criteria on seller names, is thousands of choices. I want to create expression for Buyer 2 that is buying only from one Seller.

Many thanks for your help

[Mauritz_SA · Accepted solution]

Hi there

Something like this should work:

Count(Distinct {<Buyer = {'=AGGR(Count(Distinct Seller),Buyer)=1'}>} Buyer)

Regards,

Mauritz

[Fiorrie]

Hello Mauritz

Thanks so much is working! Would you be able to help also with another formula. I need:

1. Number of Buyers that did not buy anything within the last 4 Quarters at specific Seller

2. If Buyer stopped buying from Seller 1 current quarter and started buying from another Seller 2 next quarter

Many thanks!

[Mauritz_SA]

Hi Fiorrie

I am sure I can try and help. Will you maybe post a sample of your data and the expected results? It just makes it easier to test.

Regards,

Mauritz

[Fiorrie]

Hi Mauritz

Of course, below examples to my points. Any questions let me know.

Thanks again for looking into this

[Mauritz_SA]

Hi Fiorrie

Sorry for the slow reply. How do you know the current quarter and the previous quarter(s)? Are you using the autocalendar? In other words do you have something like a year field and a quarter field? Just want to make sure that the solution works in your environment.

Regards,

Mauritz

[Fiorrie]

Hi Mauritz

Yes yes, I have a flag for it, Current Quarter = 0, Previous Quarter = 1, the same for year/week everything is autocalendar.

[Mauritz_SA · Accepted solution]

Hi Fiorrie

It is quite difficult to understand exactly what your data model looks like. I recommend adding in a field which gives you the quarters ago where the current quarter is 0 and the previous quarter is 1, etc. Then expression below should help you with your second questions:

Count(Distinct {<Buyer = {"=AGGR(If(Sum({<QuartersAgo = {'0'}>}Quantity)>0 AND Sum({<QuartersAgo = {'1','2','3','4'}>}Quantity)=0,1,0),Buyer)=1"}>} Buyer)

I think maybe you should ask a new question on the community with an actual dataset so that people can try and help you.

Good luck.

Mauritz

[Fiorrie]

Hi Mauritz

You are the best its working!!! 🙂 Many thanks

I was wondering what does it mean =0,1,0?

Also the first question about switching of sellers any clue?

Thanks again! 🙂

[Mauritz_SA]

Hi Fiorrie

I was wondering what does it mean =0,1,0?

It is part of the if statement so it is basically a flag that could be checked. If the sum of the sales was 0, then 1, else 0. Then later you only count the ones which were flagged as a 1.

Also the first question about switching of sellers any clue?

I am not sure what the question is, but if you are looking for buyers who bought from Seller 1 in the previous quarter and now buys from Seller 2 then this should work (if you have the QuartersAgo flag again):

Count(DISTINCT {<Buyer = {"=ONLY({<QuartersAgo={'1'}>} Seller) ='Seller 1' AND ONLY({<QuartersAgo={'0'}>} Seller) ='Seller 2'"}>}Buyer)

Hope this helps.

Mauritz