Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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)
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.
Dimension:
aggr(only({<Users=p(Recipient)>}Users),Users)
Measure: sum({<User=p(Sender)>}point)
Not work 😞
Hello
Anyone can help me??? :'(
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))
Thank you so muchh, vinieme12
It work now.