Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum(Aggr(sum()) or Aggr(Sum(),Key)

Hi everyone

In my table :

CNumber     Amount

1                    100

1                    100

2                    200

3                    300

4                    400

5                    500

5                    500

CNumber is my primary key. At times I have amount repeating twice in front of it.

I want a sum of the amount in a way that duplicate entry is not considered,

For example Sum of 1 should be 100 and sum of 5 should be 500.

But i keep getting 200 and 1000 in stead.

I tried using Sum(aggr(Sum(Amont),CNumber)) and Aggr(Sum(Amount),CNumber)

I am still unable to obtain the desired result.

Please help

Thanks,

Utsav

9 Replies
swuehl
MVP
MVP

Try

=Sum(aggr( only(Amount),CNumber))

Not applicable
Author

Hi swuehl ,

Sum(

 

{{$<Flag={ABC},Attribute={'MNO'},status={,'Sx','Fc'},Code-={234,456},[Month (#)]={'<=$(=Max(Month))'}>} Amount)

this is how i calculate my amount..I dont think I can use Only over here.

Not applicable
Author

Hi,

Try with this.

Sum({{$<Flag={ABC},Attribute={'MNO'},status={,'Sx','Fc'},Code-={234,456},[Month (#)]={'<=$(=Max(Month))'}>} aggr(only(CNumber), Amount))

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Your expression above is correct just missing ONLY. SO if you tweak it and say something like.

=sum(Aggr(only(Amount),[CNumber ]))

Hope this is helpful

Best Regards,

Gabriel

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Back to the basics... You want to summarize distinct amounts, right? Simply add keyword DISTINCT inside of your sum:

Sum( DISTINCT

{{$<Flag={ABC},Attribute={'MNO'},status={,'Sx','Fc'},Code-={234,456},[Month (#)]={'<=$(=Max(Month))'}>} Amount)

This is assuming that the CNumber is one of your Dimensions or directly associated to one of your dimensions. If not, you need to use an AGGR. In the aggr, you need to use all of your other Chart Dimensions, and also CNumber. So, the final expression could look like this:

SUM(  AGGR(

     Sum( DISTINCT {{$<Flag={ABC},Attribute={'MNO'},status={,'Sx','Fc'},Code-={234,456},[Month (#)]={'<=$(=Max(Month))'}>} Amount)

, CNumber, Dim1, Dim2, Dim3, ...)

cheers,

Oleg Troyansky

Not applicable
Author

Sum(DISTINCT aggr(Sum(Amont),CNumber))

Not applicable
Author

Hi, I also think the expresseion is correct, even "only" is not needed. I changed some value in inputs (500 -> 501), in order to show the difference between Sum(Aggr and Aggr(Sum...

While "Aggr(Sum..." calculates the sum based on CNumber, "Sum(Aggr..." requires unambiguity for Amount (see attachment).

BR, Hamid

aapurva09
Creator
Creator

Hi,

I was also getting values in double and below expression worked for me:

Sum(aggr(sum( {< >} Amount),CNumber))

balabhaskarqlik

May be this:

=sum(aggr(sum(DISTINCT [Amount]), [CNumber]))