Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Use Variable for Calander Range in AutoGenerate

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

1 Solution

Accepted Solutions

Use Variable for Calander Range in AutoGenerate

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

1 Reply

Use Variable for Calander Range in AutoGenerate

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