Add Value from Last Year in Table

[microwin88x]

Hello,

I have the following table:

     - Period: YYYYMM

Period	Value
201501	10
201502	15
201503	5
201504	20
201505	25
201506	30
201507	100
201508	150
201509	135
201510	500
201511	200
201512	235
201601	5
201602	15
201603	20
201604	50
201605	45
201606	95
201607	70
201608	750
201609	200
201610	250
201611	380
201612	355

What I need is to create a table like this:

Period	Value	Previous_Value
201601	5	10
201602	15	15
201603	20	5
201604	50	20
201605	45	25
201606	95	30
201607	70	100
201608	750	150
201609	200	135
201610	250	500
201611	380	200
201612	355	235

This table contains last 12 months with the corresponding VALUE and the PREVIOUS_VALUE from last year.

Do you know how could I do this, directly on my Straight Table as an expression?

Thank you!!!

[sunny_talwar · Accepted solution]

One way is to use the Above() function

[sunny_talwar]

Second method using The As-Of Table

[Frank_Hartmann]

or just add this line in skript to your table:

peek(Value,-12) as prevValue

hope this helps

[ziadm]

Fourth Way

make the next year as Key and left join resident

Table1:

load * ,

AddYears(Date#(Year, 'YYYYMM'),1) as NextYear,

Date#(Year, 'YYYYMM') as YearMonth;

LOAD * Inline [

Year, Value

201501, 10

201502, 15

201503, 5

201504, 20

201505, 25

201506, 30

201507, 100

201508, 150

201509, 135

201510, 500

201511, 200

201512, 235

201601, 5

201602, 15

201603, 20

201604, 50

201605, 45

201606, 95

201607, 70

201608, 750

201609, 200

201610, 250

201611, 380

201612, 355 ];

left join

load NextYear as YearMonth,

Value as LastYearValue

Resident Table1;

[ziadm]

My way faster no calculation if you are loading big size data also you can combine another Key with YearMonth Field.. For example your key could be YearMonth + ProductID so the aggregation by Product