Set Analysis (Rolling Expression)

[qlik4asif]

Hello Guys,

Help me in below set expression , not able to achieve this from long time.

Requirement is to get the Rolling of sales

Time period to considered is (Previous 9 months + Current Month + Next 2 months)

Below is the sample data, mentioned rolling period below for understanding.

Same KPI is Used in Tables charts, KPI box.

Expression i tried is as below.

Sum({<Date = {">=$(=MonthStart(AddMonths(Date(Max(Date)),-9)))
<=$(=Monthend(AddMonths(Date(Max(Date)),2)))"}>}Sales)

Issue:Max date should calculate row level in table, but above expression max date is calculated on whole App.

I can't use Range sum because some months data is missing and also the same kpi is mentioned in KPI Box and many charts with lowest granularity.

Thanks in Advance 🙂

Product	Date	Year	Month	Sales	Sales_Pred	Rolling Period to be considered(Prev 9 + CurMon + Next 2)
P01	4/1/2020	2020	4	100	330	04/2020 to 06/2020
P01	5/2/2020	2020	5	110	460	04/2020 to 07/2020
P01	6/2/2020	2020	6	120	600	04/2020 to 08/2020
P01	7/3/2020	2020	7	130	750	04/2020 to 09/2020
P01	8/3/2020	2020	8	140	910	04/2020 to 10/2020
P01	9/3/2020	2020	9	150	1080	04/2020 to 11/2020
P01	10/4/2020	2020	10	160	1280	04/2020 to 12/2020
P01	11/4/2020	2020	11	170	1460	04/2020 to 01/2021
P01	12/5/2020	2020	12	200	1620	04/2020 to 02/2021
P01	1/5/2021	2021	1	180	1760	04/2020 to 03/2021
P01	2/5/2021	2021	2	160	1780	05/2020 to 04/2021
P01	3/8/2021	2021	3	140	1770	06/2020 to 05/2021
P01	4/8/2021	2021	4	120	1650	07/2020 to 06/2021
P01	5/9/2021	2021	5	100	1520	08/2020 to 07/2021
P02	4/1/2020	2020	4	80	180
P02	5/2/2020	2020	5	60	200
P02	6/2/2020	2020	6	40	330
P02	7/3/2020	2020	7	20	490
P02	8/3/2020	2020	8	130	680
P02	9/3/2020	2020	9	160	900
P02	10/4/2020	2020	10	190	1150
P02	11/4/2020	2020	11	220	1430
P02	12/5/2020	2020	12	250	1740
P02	1/5/2021	2021	1	280	2080
P02	2/5/2021	2021	2	310	2370
P02	3/8/2021	2021	3	340	2710
P02	4/8/2021	2021	4	370	2670
P02	5/9/2021	2021	5	400	2650

[Anil_Babu_Samineni]

Perhaps this?

Sum(Aggr(Sum({<Date = {">=$(=MonthStart(AddMonths(Date(Max(Date)),-9)))
<=$(=Monthend(AddMonths(Date(Max(Date)),2)))"}>}Sales), Dim1, Dim2))

[qlik4asif]

Thanks for the reply ,

But expression is not working