8 Replies Latest reply: May 9, 2018 6:14 AM by Sasidhar Parupudi

# Distinct in set analysis

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!

• ###### Re: Distinct in set analysis

try

Pro,    Order Amount

A, 1

A, 1

B, 2

C, 1

];

• ###### Re: Distinct in set analysis

I'm not able to do this in data load script. Is it feasible in set analysis?

• ###### Re: Distinct in set analysis

looking for this?

sum(DISTINCT  [Order Amount])

• ###### Re: Distinct in set analysis

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)

• ###### Re: Distinct in set analysis

try this

=sum(aggr( sum(Distinct [Order Amount]),Pro))

• ###### Re: Distinct in set analysis

May be this

Sum(Aggr([Amount], Order))

• ###### Re: Distinct in set analysis

Hi

Try below.

Table:

Pro,    Order Amount

A, 1

A, 1

B, 2

C, 1

];

FinalTable:

[Order Amount],

Pro,

If(Pro <>Previous(Pro), 'NoDuplicate') as Flag

Resident Table;

DROP Table Table;

in front end  use :   =sum({<Flag={"NoDuplicate"}>} [Order Amount])

• ###### Re: Distinct in set analysis

Aggr(Sum(Amount),Order )