previous period in script

[#1]

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

[its_anandrjs]

Hi,

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

Regards

Anand

[MK_QSL]

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;

[#4]

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

[#5]

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

[MK_QSL]

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());

[#7]

That's it:) THANKS!!!