Use Variable for Calander Range in AutoGenerate

Wed, 02 Nov 2011 09:39:19 GMT

Hi,

I am loading a period table which just has the financial year values in which I load as follows:

PERIODS:

LOAD

Year

FROM

$(zDataPath)\Periods.qvd(qvd);

Then to determine the amount of Years I have available I do the following:

PERIOD:

Load

Max(Year) - Min(Year) + 1 as YR_Range

Resident PERIODS;

Now I use this code for the Calander:

Load

TODAY()-ROWNO()+365 AS PeriodDate,

YEAR(TODAY()-ROWNO()+365) AS YEAR,

MONTH(TODAY()-ROWNO()+365) AS MONTH,

DAY(TODAY()-ROWNO()+365) AS DAY,

YEAR(TODAY()-ROWNO()+365)*100 + MONTH(TODAY()-ROWNO()+365) AS YM,

YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YR",

MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "PERIOD",

YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3))*100 + MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YP"

AUTOGENERATE (365*3);

My problem is that for the AutoGenerate command I want to load the amount of years based on what is the Max - Min of Year plus 1 into a variable and use that variable for the AutoGenerate command, which should be something like this

AUTOGENERATE (365*v_Range);

Regards

Jimmy

Use Variable for Calander Range in AutoGenerate

kaushiknsolanki — Wed, 02 Nov 2011 09:47:22 GMT

Hi,

Try this code.

PERIOD:

Load

Max(Year) - Min(Year) + 1 as YR_Range

Resident PERIODS;

let v_Range = peek('YR_Range',0,'PERIOD');

Load

TODAY()-ROWNO()+365 AS PeriodDate,

YEAR(TODAY()-ROWNO()+365) AS YEAR,

MONTH(TODAY()-ROWNO()+365) AS MONTH,

DAY(TODAY()-ROWNO()+365) AS DAY,

YEAR(TODAY()-ROWNO()+365)*100 + MONTH(TODAY()-ROWNO()+365) AS YM,

YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YR",

MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "PERIOD",

YEAR(ADDMONTHS(TODAY()-ROWNO()+365,3))*100 + MONTH(ADDMONTHS(TODAY()-ROWNO()+365,3)) AS "FIN YP"

AUTOGENERATE (365*$(Range));

Regards,

Kaushik Solanki

topic Use Variable for Calander Range in AutoGenerate in QlikView

Use Variable for Calander Range in AutoGenerate

Use Variable for Calander Range in AutoGenerate