Submit your use case or success story to the 2023 edition of the Dataiku Frontrunner Awards ENTER YOUR SUBMISSION

Question for formula

YukiN
Dataiker
Question for formula

Hello,

I have data with columns "Date", "Cusotmer" and "This month purchase count" as shown in the table below, is it possible to calculate the "Last month purchase count" with the Prepare recipe?

 

Date Customer This Month Purchase Count Last Month Purchase Count
May-22 A 5 1
May-22 B 3 4
May-22 C 7 6
Apr-22 A 1 2
Apr-22 B 4 5
Apr-22 C 6 1
Mar-22 A 2  
Mar-22 B 5  
Mar-22 C 1  
0 Kudos
2 Replies
ZachM
Dataiker

Hi @YukiN,

You can do this using a Window recipe.

If your Date column is a string, first you'll need to parse it to a date. You can do this by using a "Parse date" step in a Prepare recipe as shown below:

B4E0BAB2-7968-4FB4-97ED-15681B517182_1_201_a.jpeg

Be sure that the type of the column is set to "date" (highlighted above).

 

Once you have a parsed date column, you can create a Window recipe with the following settings:

 Window definitions:E28393A7-C34D-4224-A335-655AE7871C23.png

Aggregations (be sure to select the "Lag" aggregation): 

34D4F658-6786-4052-8886-6D0483C60E89.png

 

Here's what the output dataset looks like:

36DEDEE9-1CCF-4B95-AFC6-BA0A78B58DBB.png

 

Reference documentation:

 

Thanks,

Zach

0 Kudos
YukiN
Dataiker
Author

Wow, thank you very much @ZachM !! I could do it.  

0 Kudos