Bring previous record data to the current record

Question & Answer
, , , ,
1

Sample Data:
Date ASIN Shipped_Revenue Shipped_COGS Shipped_Units Customer_Returns priceperunit
2023-05-31 xyx 317 290 2 0 159
2023-06-11 xx 185 145 1 0 185
2023-06-18 x 185 145 1 0 185
2023-07-05 yy 187 145 1 0 187

For the provided dataset, I need to create two new columns:

  1. PI (Previous Shipped Units):

    • This column should display the shipped units from the previous row. The first row will have null since there is no previous data.

  2. PP (Previous Price per Unit):

    • This column should display the price per unit from the previous row. The first row will have null as there is no previous data.

Output:

Date ASIN Shipped_Revenue Shipped_COGS Shipped_Units Customer_Returns priceperunit PI PP
2023-05-31 xxx 317 290 2 0 159 null null
2023-06-11 xxx 185 145 1 0 185 2 159
2023-06-18 xxx 185 145 1 0 185 1 185
2023-07-05 xxx 187 145 1 0 187 1 185

In both cases, the first row will contain null because it lacks previous data.
Here i need the previous records values to be avalible in the current records and i dont need any aggregation .
How to acheive this ?

2

Hi @Vamsi_Prasad

Using the lag function, you can reference the previous row’s value. Create the two calculated fields, PI (Previous Shipped Units) and PP (Previous Price per Unit). Refer to the following example and adjust it to your requirements if necessary.

Example: (Syntax may vary)

PI (Previous Shipped Units)

lag(SUM({Shipped_Units}), [Date ASC], 1)

PP (Previous Price per Unit)

lag(SUM(priceperunit), [Date ASC], 1)

image
image1192×201 16 KB

image

image

Please refer the below documentation this might be helpful for you.

1 Like
3

Hi @Vamsi_Prasad,
It’s been awhile since we last heard from you; did you have any additional questions or did the response above provide the desired insight to reach your solution?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @Vamsi_Prasad,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!