Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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

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. 

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