AGGR Fuction

[erivera10]

Hi,

I need help, I have this por expression Double,

IF(SUM(AGGR(sum(Amount),Group))>9,

Sum(Aggr(sum(9),Group)),

Sum(Aggr(sum(Amount),Group)))

and this por Triple

IF(SUM(AGGR(SUM(Amount),Group))<9,0,

Sum(Aggr(Sum(Amount)-9,Group)))

I have this natural table:

Group	Amount
A	1
A	2
A	4
B	2
B	3
C	1
C	4
D	4
D	6
E	5
E	6
F	1
F	2
F	3
F	4

But I need to separete de data of the Amount, for example, Sum Value F is 10, so for doubles is 9 and for Triples 1,

the maximum for Doubles is 9, and Triples don´t have limits,

The total results are this

Group	SUM(AGGR(SUM(Amount),Group))
	48
A	7
B	5
C	5
D	10
E	11
F	10

And Amount in Doubles and Triples need to be this

Group	Dobles	Triples
	63	-6
A	7	0
B	5	0
C	5	0
D	9	1
E	9	2
F	9	1

But the total sum not is true, Doubles=44 and Triples=4, I don´t Found the solution, and subsequently, I will to do a chart whit the velocimeter, Thanks a lot.

[Anil_Babu_Samineni]

Attached

[sunny_talwar · Accepted solution]

Suggest to slightly simplify the expressions

Doubles

=Sum(Aggr(RangeMin(9, Sum(Amount)), Group))

Triples

=Sum(Aggr(RangeMax(0, Sum(Amount)-9), Group))

[erivera10]

Sunny,

Thanks a lot, its working, and very simple.

[erivera10]

Anil,

Thanks a lot, its work, too.