Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead 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

Please help me with SET analysis formula

MonthNoVersionSales
4150
4260
4370
5135
5240
5355
5460
5575
6115
6225
6340
6445
6560
6665
6770
3 Replies
tresesco
MVP
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))

prieper
Master II
Master II

Restricting to last 3 monthes might read:

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

HTH Peter

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