Survey banner
The Dataiku Community is moving to a new home! New posts are now disabled and the community will shortly be in temporary read only mode:

# Sum for the last 12 months

Solved!
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:

 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

1 Solution
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.

4 Replies
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.

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

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

Level 2
Author

It works very well.

Thanks

Tags (2)