Question for formula

YukiN

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

Zach

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:

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:

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

Here's what the output dataset looks like:

Reference documentation:

• Concept: Window Recipe
• Concept: Date Handling in Dataiku

Thanks,

Zach

YukiN

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