Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))
Restricting to last 3 monthes might read:
SUM({$< MonthNo = {">=$(=MAX(MonthNo) -2)"} >} AGGR(FIRSTSORTEDVALUE({$<MonthNo = >} Sales, -Version), MonthNo))
HTH Peter
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))