topic Re: previous period in script in QlikView

previous period in script

Mon, 13 Apr 2015 12:24:41 GMT

Hi,

I would like to create a data model where the sum(measure) of previous period (measure_LY) is pre-calculated in the script:

so if i have the following input table...

period	group	measure
1-1-2015	A	80
1-1-2015	B	90
1-1-2015	C	15
1-1-2014	D	30
1-1-2014	A	20

... the the parallel period is defined as follow

period	prev_period
1-1-2015	1-1-2014

the desired output should be

period	group	measure	measure LY
1-1-2015	A	80	20
1-1-2015	B	90
1-1-2015	C	15
1-1-2015	D		30

please note the period can be anything from day to year interval

please help...

Dror

Re: previous period in script

its_anandrjs — Mon, 13 Apr 2015 12:35:06 GMT

Hi,

Better you use SET analysis expression here in the chart for previous value fetching.

Regards

Anand

Re: previous period in script

MK_QSL — Mon, 13 Apr 2015 12:35:56 GMT

Data:

Load

Date(Date#(period,'D-M-YYYY')) as period,

group,

measure

Inline

[

period, group, measure

1-1-2015, A, 80

1-1-2015, B, 90

1-1-2015, C, 15

1-1-2014, D, 30

1-1-2014, A, 20

];

Left Join(Data)

Load

AddYears(period,1) as period,

group,

measure as LastYearMeasure

Resident Data;

Re: previous period in script

Mon, 13 Apr 2015 12:45:39 GMT

Manish,

Thanks for your reply.

You miss however group D. group D existed in 2014 but not in 2015 and should be taken in to account in the total of LastYearMeasure

That's the core of my problem...

Re: previous period in script

Mon, 13 Apr 2015 12:57:14 GMT

I know it can be solved on a chart level but from various reasons i would like to keep it on the script level

Re: previous period in script

MK_QSL — Mon, 13 Apr 2015 13:30:49 GMT

Data:

Load

Date(Date#(period,'D-M-YYYY')) as period,

group,

measure

Inline

[

period, group, measure

1-1-2015, A, 80

1-1-2015, B, 90

1-1-2015, C, 15

1-1-2014, D, 30

1-1-2014, A, 20

];

Outer Join(Data)

Load

AddYears(period,1) as period,

group,

measure as LastYearMeasure

Resident Data Where Year(AddYears(period,1)) <= Year(Today());

Re: previous period in script

Tue, 14 Apr 2015 06:49:10 GMT

That's it:) THANKS!!!