Sum for the last 12 months

Ineedi2
Ineedi2 Partner, Dataiku DSS Core Designer, Registered Posts: 9 Partner

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

Best Answer

  • Manuel
    Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭
    Answer ✓

    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.

Answers

  • Ineedi2
    Ineedi2 Partner, Dataiku DSS Core Designer, Registered Posts: 9 Partner

    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
    Ineedi2 Partner, Dataiku DSS Core Designer, Registered Posts: 9 Partner

    It works very well.

    Thanks

  • RAMAN
    RAMAN Dataiku DSS Core Designer, Registered Posts: 3 ✭✭✭

    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

Setup Info
    Tags
      Help me…