Use Variable for Calander Range in AutoGenerate

[#1]

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

[kaushiknsolanki · Accepted solution]

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