Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

May be this:

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

or

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

or

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

swuehl
MVP
MVP

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
Author

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

Not applicable
Author

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. 

sunny_talwar

Check the attached or share a sample of yours

Capture.PNG

Not applicable
Author

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
Author

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

Saravanan_Desingh

One more expression on Sunny's App..

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