Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
MVP
MVP

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

Try

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

Not applicable

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

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

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

Hi,

Try with this.

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

Partner
Partner

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

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

MVP & Luminary
MVP & Luminary

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

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

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

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

Not applicable

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

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
Contributor

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

Hi,

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

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

balabhaskarqlik
Honored Contributor

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

May be this:

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