Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

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!

1 Solution

Accepted Solutions
sureshqv
Esteemed Contributor III

Re: Distinct in set analysis

try this

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

8 Replies
sureshqv
Esteemed Contributor III

Re: Distinct in set analysis

try

LOAD Distinct * INLINE [

Pro,    Order Amount

    A, 1

    A, 1

    B, 2

    C, 1

];

wanyunyang
Contributor

Re: Distinct in set analysis

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

sureshqv
Esteemed Contributor III

Re: Distinct in set analysis

looking for this?

sum(DISTINCT  [Order Amount])

wanyunyang
Contributor

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)

sureshqv
Esteemed Contributor III

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))

arunaerra
Contributor

Re: Distinct in set analysis

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])

sasiparupudi1
Honored Contributor III

Re: Distinct in set analysis

Aggr(Sum(Amount),Order )

Community Browser