topic Re: Load last 12 months with data in QlikView

Load last 12 months with data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following file of excel and qwd.

I would like to get the last 12 months with data via script.

So I would like to get the following data.

201606 21,1%

201607 18,6%

201608 18,2%

201609 23,1%

201610 24,9%

201611 28,8%

201612 24,9%

201701 29,0%

201702 31,4%

201703 30,6%

201704 28,1%

201705 31,5%

thank you

Re: Load last 12 months with data

Anonymous — Wed, 20 Sep 2017 17:38:25 GMT

Hi Pablo,

You can use month to day function

Re: Load last 12 months with data

Anil_Babu_Samineni — Thu, 21 Sep 2017 01:27:31 GMT

Here you go

Script:

Sample:

LOAD Date,

margin

FROM

margin.xls

(biff, embedded labels, table is Sheet1$) Where Date >= $(vRunning12Months) and Date <= $(vLast);

LET vRunning12Months = Peek('Date',-13,'Sample');

LET vLast = Peek('Date', -2, 'Sample');

PFA

Re: Load last 12 months with data

its_anandrjs — Thu, 21 Sep 2017 04:59:02 GMT

Try to load it dynamically suppose if in your table dates are not in the proper ascending order then you not get max date, so try this ways by finding maxdate and then load data from maximum date upto 12 months YYYYMM

Data:

LOAD Date, margin

FROM margin.xls (biff, embedded labels, table is Sheet1$);

MaxDateTab:

LOAD Max(Date) as MaxDate Resident Data;

LET vLast12Month = Date( AddMonths( Date#( Peek('MaxDate',0,'MaxDateTab'), 'YYYYMM'),-12), 'YYYYMM');

LET vMaxDate = Peek('MaxDate', 0, 'MaxDateTab');

NoConcatenate

LOAD * Resident Data Where Date > $(vLast12Month);

DROP Table Data;

OupPut