topic Re: Add Value from Last Year in Table in QlikView

Add Value from Last Year in Table

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Add Value from Last Year in Table

sunny_talwar — Fri, 17 Mar 2017 18:50:08 GMT

One way is to use the Above() function

Re: Add Value from Last Year in Table

sunny_talwar — Fri, 17 Mar 2017 18:56:50 GMT

Second method using The As-Of Table

Re: Add Value from Last Year in Table

Frank_Hartmann — Fri, 17 Mar 2017 19:06:00 GMT

or just add this line in skript to your table:

peek(Value,-12) as prevValue

hope this helps

Re: Add Value from Last Year in Table

ziadm — Fri, 17 Mar 2017 19:28:00 GMT

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;

Re: Add Value from Last Year in Table

ziadm — Fri, 17 Mar 2017 20:10:41 GMT

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