Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Previous 3 month formula

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

 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
3 Replies
MVP

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

Master II

Restricting to last 3 monthes might read:

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

HTH Peter

MVP

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

Community Browser