topic Re: Previous 3 month formula in QlikView

Previous 3 month formula

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Previous 3 month formula

tresB — Thu, 27 Apr 2017 08:38:10 GMT

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

Re: Previous 3 month formula

prieper — Thu, 27 Apr 2017 08:58:43 GMT

Restricting to last 3 monthes might read:

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

HTH Peter

Re: Previous 3 month formula

tresB — Thu, 27 Apr 2017 09:12:32 GMT

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

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