Looking for a solution

[pthai]

Hello guys,

I have a problem to calculate the point used of a recipent. I need to know the point which recipent consumed.

Here is an example for recipent B

Here is my expected result

Recipent	Sum of Used Point
A	0
B	5
F	0
K	0

Could anyone help me find the answer here? Thank you.

[vinieme12]

You will need to create another table linked to your raw data 

Raw:

Load *

, Sender&'-'& Recipient as keyfield 

From xxxxx;

SenderReceivers:

Load

Sender as Users, keyfield 

Resident Raw;

Concatenate(SenderReceivers)

Load

Recipient as Users, keyfield 

Resident Raw;

Then use the Users field from the new table in your charts

Dimension: only({<Users=p(Recipient)>}Users)

Measure: sum({<User=p(Sender)>*<User=p(Recipient)>}point)

[pthai]

Hi Vini,

Thank you so much for your response. But, it not work. Here is my updated file with your idea.

I show an example for the recipent B as below.

[vinieme12]

Dimension:

aggr(only({<Users=p(Recipient)>}Users),Users)

Measure: sum({<User=p(Sender)>}point)

[pthai]

Not work 😞

[pthai]

Hello

Anyone can help me??? :'( 

[vinieme12 · Accepted solution]

here is the updated script and expression

Raw:
Load *,Sender&'-'& Recipient as keyfield Inline [
Recipient,Sender,date,point
A,B,2022-04-01,3
A,C,2022-04-13,2
B,E,2022-04-10,1
B,D,2022-04-12,2
F,H,2022-04-22,2
K,B,2022-04-21,2
];

SenderReceivers:
Load
Sender as Users,'Sender' as type, keyfield
Resident Raw;
Concatenate(SenderReceivers)
Load
Recipient as Users,'Recipient' as type, keyfield
Resident Raw;

InChart

Dimension = =aggr(only({<type={'Recipient'}>}Users),Users)

Expression = if(sum({<type={'Sender'}>}point) and sum({<type={'Recipient'}>}point) , sum({<type={'Sender'}>}point) ,sum(0))

[pthai]

Thank you so muchh, vinieme12

It work now.