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

[#1]

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

[swuehl]

Try

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

[#3]

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.

[#4]

Hi,

Try with this.

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

[Gabriel]

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]

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

[#7]

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

[#8]

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]

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