Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Sum for distinct values

Hi all,
I have a dataset similar to below:

Trip Id     Amount

a              10

b              10

b              10

b              10

c              5

I want sum(amount) for distinct trip ids which should be: 25(10+10+5)
is aggr() an option?

Any solutions?
Thanks in advance

8 Replies
MVP
MVP

Re: Sum for distinct values

May be this:

Sum(Aggr(Only(Amount), [Trip Id]))

or

Sum(Aggr(Avg(Amount), [Trip Id]))

or

Sum(Aggr(Sum(DISTINCT Amount), [Trip Id]))

MVP
MVP

Re: Sum for distinct values

Consider changing your data model in a way your fact table only shows a single record per fact value, i.e. try to avoid data duplication caused by e.g. joining tables.

To Join or not to Join

Don't join - use Applymap instead

Then a simple Sum(Amount) should return what you want.

It's better to spent the time on data modelling than fixing things later in (more complex) expressions.

Not applicable

Re: Sum for distinct values

Thanks for the reply! Will all of them lead to same result? I checked for the first 2, it doesn't

Not applicable

Re: Sum for distinct values

Thanks Swuehl!
However, there are no overall duplicate rows. There are other columns(not mentioned here)  which make the entire row unique. I don't want to create a separate table just for a distinct sum. 

MVP
MVP

Re: Sum for distinct values

Check the attached or share a sample of yours

Capture.PNG

Not applicable

Re: Sum for distinct values

Hi,

Instead of using expression:

=Sum(Amount)

cant you just use expression:

=Sum(DISTINCT Amount)

That will only total the unique Amount Values.

Bri.

Not applicable

Re: Sum for distinct values

Hi,
That would give the result as 15(according to the data set in my question). Two different ids can have same amount.

saran7de
Contributor III

Re: Sum for distinct values

One more expression on Sunny's App..

=Sum(Aggr(FirstSortedValue(Amount,Key),[Trip Id]))