Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some Orders and Amounts with duplicates:
Order Amount
A 1
A 1
B 2
C 1
I'm trying to write a function to calculate SUM(Amount) for different Orders. The result should be: 1+2+1=4. How should I do it in set analysis?
Thanks for help!
try
LOAD Distinct * INLINE [
Pro, Order Amount
A, 1
A, 1
B, 2
C, 1
];
I'm not able to do this in data load script. Is it feasible in set analysis?
looking for this?
sum(DISTINCT [Order Amount])
Then it will count 1 only once. 1 should be counted twice, once for A once for C.
I'm trying to find something like: SUM({<DISTINCT Order>} Amount)
try this
=sum(aggr( sum(Distinct [Order Amount]),Pro))
May be this
Sum(Aggr([Amount], Order))
Hi
Try below.
Table:
LOAD * INLINE [
Pro, Order Amount
A, 1
A, 1
B, 2
C, 1
];
FinalTable:
LOAD
[Order Amount],
Pro,
If(Pro <>Previous(Pro), 'NoDuplicate') as Flag
Resident Table;
DROP Table Table;
in front end use : =sum({<Flag={"NoDuplicate"}>} [Order Amount])
Aggr(Sum(Amount),Order )