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

[Fiorrie]

Hi Mauritz

Got it thank you. 

Over here do I always have to specify 'Seller 1'? Because there is many, no way to do it automatically?

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

[sunny_talwar · Accepted solution]

You can check using this for if there were two sellers involved in the two quarters

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

[Fiorrie]

Hi Sunny

Seems working thank you! 🙂

[Fiorrie]

Hi Mauritz

Many thanks again for your help. One more question to this topic, I am trying to replace the static quarter flag by dynamic, so if user changes the quarter selection it dynamically moves for both current and 4 quarter back re-calculating everything. The formula in green is the original (working) and in red is not working within this whole Aggr, perhaps it is missing some additional question marks? I tried to put it in variable but still empty. 

When I am using it like this, its working:

Count({1<[Quarter ID] = {">= $(=Max([Quarter ID])-4) <= $(=Max([Quarter ID])-1)"}>} distinct [Buyer])

Thank you!

[Mauritz_SA]

Hi Fiorrie

The following aggregation should work presuming you still have the flags for the quarters ago (and the current one being 0 and the previous quarter being 1, two ago being 2, etc):

=AGGR(If(Sum({<[Quarters Ago] = {"$(=Min([Quarters Ago]))"}>} [# Quantity]) > 0 AND Sum({<[Quarters Ago] = {">$(=Min([Quarters Ago])) <= $(=Min([Quarters Ago])+4)"}>} [# Quantity])=0,1,0),Buyer)

Now you just need to wrap it in the count. To work with quotes within quotes is tricky and I didn't have time to get the full solution now. Another note is that you might want to make sure that the second expression (looking at the previous 4 months) looks at the entire data set and not only those in your current selection (so if you only select one quarter then the max will update, but it needs to look at the previous four quarters as well even if they aren't selected).

Unfortunately I can't spend too much time on this :).

Regards,

Mauritz