Sum for the last 12 months

Solved!
Ineedi2
Level 2
Sum for the last 12 months

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:

 

iddaterevenuExpected compute
12020121010
12020112030
12021013060
120210240100
120210350150
120210460210
120210570280
120210680360
120210790450
1202108100550
1202109110660
1202110120780
1202111130900
12021121401020
220201255
22020111520
22021012545
22021034590
220210455135
220210565200
220210675275
220210785360
220210895455
2202109105560
2202110115675
2202112135800

 

 

Thanks for your time 

Ineedi2

0 Kudos
1 Solution
Manuel
Dataiker Alumni

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.

View solution in original post

0 Kudos
4 Replies
Manuel
Dataiker Alumni

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.

0 Kudos
Ineedi2
Level 2
Author

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

0 Kudos
RAMAN
Level 1

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

 

0 Kudos
Ineedi2
Level 2
Author

It works very well. 

Thanks

 

0 Kudos