Sum for the last 12 months

Ineedi2

Hello,

I want to get the sum of revenues for the last 12 months for an id.

The problem is for some month i dont have data (so empty row) so i can't use the windows frame=12.

Do you any idea to solve it ?

For your information i have already see this post:

Solved: Using Window recipe and moving average - Dataiku Community

But the problem in my example is i can have empty data for 1 month and in this case i just want the last 11 month sum (example id=2)

I have reproduced on Excel the output I want:

id	date	revenu	Expected compute
1	202012	10	10
1	202011	20	30
1	202101	30	60
1	202102	40	100
1	202103	50	150
1	202104	60	210
1	202105	70	280
1	202106	80	360
1	202107	90	450
1	202108	100	550
1	202109	110	660
1	202110	120	780
1	202111	130	900
1	202112	140	1020
2	202012	5	5
2	202011	15	20
2	202101	25	45
2	202103	45	90
2	202104	55	135
2	202105	65	200
2	202106	75	275
2	202107	85	360
2	202108	95	455
2	202109	105	560
2	202110	115	675
2	202112	135	800

Thanks for your time

Ineedi2

Manuel

Hi,

One suggestion is to try to define the Window based on value and not number of rows, with "limit window on a value range of the order column" option (see attached image), such as 12 months. You will need to parse your dates to use as ordering column. See also the example in this video, https://www.youtube.com/watch?v=mL4OxXK8-NY

Another suggestion is to use the time series plugin and the resampling recipe to interpolate the missing months as zeros (see attached image), which you can then follow with your window recipe.

I hope this helps.

Ineedi2

Thank you for your answer. I have found a personal approach, however, I still tested your first method which did not give the expected results.

I think I have set up the Windows recipe wrong, I will try again this morning.

Thanks

Ineedi2

It works very well.

Thanks

RAMAN

hi,

my date column has monthly level data that to start of the month. when i perform resampling plugin my dates are converting to end of the month. please see the below conversion. resampling should occur on the start of the month only. could anyone help?

2022-06-01 -->2022-05-31

2022-05-01-->2022-04-30