topic Re: Sum(Aggr(sum()) or Aggr(Sum(),Key) in QlikView

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

Fri, 16 Aug 2013 10:00:53 GMT

Hi everyone

In my table :

CNumber Amount

1 100

2 200

3 300

4 400

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

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

swuehl — Fri, 16 Aug 2013 10:10:22 GMT

Try

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

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

Fri, 16 Aug 2013 10:22:54 GMT

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.

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

Fri, 16 Aug 2013 10:30:42 GMT

Hi,

Try with this.

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

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

Gabriel — Fri, 16 Aug 2013 13:57:58 GMT

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

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

Oleg_Troyansky — Tue, 20 Aug 2013 23:08:00 GMT

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

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

Wed, 20 May 2015 04:22:46 GMT

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

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

Mon, 17 Oct 2016 10:04:49 GMT

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

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

aapurva09 — Fri, 03 Aug 2018 08:42:01 GMT

Hi,

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

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

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

BalaBhaskar_Qlik — Fri, 03 Aug 2018 09:05:51 GMT

May be this:

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