Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load last 12 months with data

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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

Capture.PNG

PFA

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Hi Pablo,

You can use month to day function

Anil_Babu_Samineni

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

Capture.PNG

PFA

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
its_anandrjs

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

OP1.PNG