Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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))