Current Month vs Prev Month script help

[qv11user]

Hi Team,

Hope everyone is safe and doing good

I have a requirement where i have data like below

OPID,Year,Month,Sales
OP1,2019,12,100
OP1,2020,1,110
OP1,2020,2,120
OP1,2020,3,130
OP1,2020,4,140
OP1,2020,5,150
OP1,2020,6,160
OP1,2020,7,170
OP1,2020,8,180
OP1,2020,9,190
OP1,2020,10,200
OP1,2020,11,210
OP1,2020,12,220
OP1,2021,1,230
OP1,2021,2,240

and i need to bring previous month sales for each month using script.
For ex for 2020/1 previous month is 2019/12

and expected output is:

OPID, Year, Month, Sales, PrevMonthSales
OP1, 2019, 12, 100, -
OP1, 2020, 1, 110, 100
OP1, 2020, 2, 120, 110
OP1, 2020, 3, 130, 120
OP1, 2020, 4, 140, 130
OP1, 2020, 5, 150, 140
OP1, 2020, 6, 160, 150
OP1, 2020, 7, 170, 160
OP1, 2020, 8, 180, 170
OP1, 2020, 9, 190, 180
OP1, 2020, 10, 200, 190
OP1, 2020, 11, 210, 200
OP1, 2020, 12, 220, 210
OP1, 2021, 1, 230, 220
OP1, 2021, 2, 240, 230

Could you please help me here?

[MayilVahanan · Accepted solution]

HI @qv11user 

Try like

Load *, Previous(Sales) as PrevSales from yoursource;

If you have multiple OPID, then try like below

Load *, If(OPID = Previous(OPID), Previous(Sales)) as PrevSales2 from yoursource;

Hope OPID ,Year & Month are in asc order.