# 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.

Thanks,

Utsav

9 Replies
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

## 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

## 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

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

Honored Contributor

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

May be this:

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