Aggregate other rows based on comparing dimension in Set Analysis

Question

I have data that looks like this:

Associate   ReferredBy  SalesQty    ReferralSales
Alice       Bob         50
Bob         Carl        60
Carl        NULL        70
Alice       NULL        80
Bob         Alice       90
Dan         Alice       10

In the ReferralSales column, I want to display the aggregate sales of the associate through referrals.

Alice is responsible for referring Bob and Dan, resulting in 100 units of sales. Bob referred Alice, 50 units. Carl referred Bob, 60 units. Dan referred no one.

How can this be achieved? I have tried:

SUM({1 Associate = {ReferredBy}} SalesQty)

Answer 1

Assuming you are trying to do this in a bar chart or similar, make ReferredBy your primary dimension and then use sum(SalesQty) as the expression. This will give you a bar chart that displays aggregate sales for each referrer which matches the breakout you mentioned. You wouldn't need set analysis at all for this.

To learn more about set analysis check here. For example, you could use this to limit your output to just referrals to one Associate: sum({$<Associate={'Bob'}>} SalesQty).

Answer 2

If you wish to show the RefferalSales in the same table then just add to your script:

left join
Load ReferredBy as Associate, sum(SalesQty) as ReferredByQty Resident T1 group by ReferredBy;

In the RefferalSales calc use: sum(ReferredByQty) and then you'll easily get

Associate   ReferredBy  SalesQty    ReferralSales
Alice           Bob         50          100
Alice           NULL        80          100
Bob             Alice       90          50
Bob             Carl        60          50
Carl            NULL        70          60
Dan             Alice       10          0

Answer 3

I know it isn't exactly what you asked but I can create the table you want with a little bit of script trickiery and an if() statement but coming up blank on the set analysis route because I can't think of a way to make the set analysis selectively break the dimensionality.

I tried this

sum({<ReferredBy={'$(above(only(SelAssociate),0))'}>} SalesQty)

because I can get the Referer on the same line as the Associatte using this above(only(SelAssociate),0) but then when the objects renders it performs the $() for the whole data set not line for line it returns nulls and then the answer is only the null() referer total. I imiagine the set state can only be evaluated once per expression. You can see the incorrect expressions in the picture as well.

This is why I used an if() because it will work line for line inside the object, but then I still needed to break the dimensionality so I created a lose hanging list of Associates in the script.

T:
load * inline [
Associate,ReferredBy,SalesQty 
Alice,Bob,50
Bob,Carl,60
Carl,,70
Alice,,80
Bob,Alice,90
Dan,Alice,10
];

Load distinct 
Associate as SelAssociate resident T;

And then the SelAssociate is the dimension and the expressions are

sum(if(Associate=SelAssociate, SalesQty))
sum(if(ReferredBy=SelAssociate, SalesQty))