Previous 3 month formula

[Anonymous]

Hi

I have data like this. I have to calculate previous 3 months values by max version of each month.

suppose if i click monthNo 6 it has to calculate the sum below bold highlighted values

Please help me with SET analysis formula

MonthNo	Version	Sales
4	1	50
4	2	60
4	3	70
5	1	35
5	2	40
5	3	55
5	4	60
5	5	75
6	1	15
6	2	25
6	3	40
6	4	45
6	5	60
6	6	65
6	7	70

[tresesco]

In a text box? Try like:

=Sum(Aggr(FirstSortedValue(Sales,  -Version),MonthNo))

Or for multiple records

=Sum(Aggr(FirstSortedValue(Aggr(Sum(Sales),Version,MonthNo),  -Version),MonthNo))

[prieper]

Restricting to last 3 monthes might read:

SUM({$< MonthNo = {">=$(=MAX(MonthNo) -2)"} >} AGGR(FIRSTSORTEDVALUE({$<MonthNo = >} Sales, -Version), MonthNo))

HTH Peter

[tresesco]

I missed on that point. However, the right expression could be like:

=SUM({$< MonthNo = {"<=$(=MAX(MonthNo)) >=$(=MAX(MonthNo) -2)"} >} AGGR(FIRSTSORTEDVALUE({$<MonthNo = >} Sales, -Version), MonthNo))

Or

=SUM({$< MonthNo = {"<=$(=MAX(MonthNo)) >=$(=MAX(MonthNo) -2)"} >} AGGR(FIRSTSORTEDVALUE( Aggr(Sum({$<MonthNo = >} Sales),Version,MonthNo) , -Version), MonthNo))