Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
grajesh_sgp
New Contributor II

last 12 months extraction in script

Hi,

I want to extract last 12 month data only by excluding current month.

for example for this month values should be from sep 2014 to aug 2015. how we can extract always last 12 months only in script level.


Fields Month and Years:

Month format:01,02,03.........

year format:2014,2015

Tags (1)
7 Replies

Re: last 12 months extraction in script

Something like:

Load ....

Resident ....

Where year = Year(MonthStart(AddMonths(Today(),-12)))  and month = month(MonthStart(AddMonths(Today(),-12))) ;

let me know

sunilkumarqv
Valued Contributor II

Re: last 12 months extraction in script

Let varMaxDate = Num(MakeDate(2014,9,1));

Load 

inyear(DateField, Monthstart($(varMaxDate)),-1) as Last12months


from datasoruce;

senpradip007
Valued Contributor III

Re: last 12 months extraction in script

Try something like

Tab:

Load *

From <datasource>

where date >= Addyear(MonthEnd(Today()), -1);

Not applicable

Re: last 12 months extraction in script

Hi,

Load *

Resident

Where year = Year(MonthStart(AddMonths(Today(),-12)))  and month = month(MonthStart(AddMonths(Today(),-12))) <Tablename> ;

it might help u.

neetha_p
Honored Contributor

Re: last 12 months extraction in script

Hi Rajesh,

Table:
Load * , Max(date) as MaxDate;
Load *

year

month

MakeDate('year','month',1) as Date

Resident....

Let varMaxDate = peek('MaxDate',0,'Table');

Let v12months = Date(addmonths ('$(varMaxDate)',-12, 'DD-MM-YYYY');


Data

load *

Resident Table

where Date >= '$(v12months)'

Drop Table Table;

Regards

Neetha

sasiparupudi1
Honored Contributor III

Re: last 12 months extraction in script

Try like this

t1:

load * Inline

[

year,month

2015,01

2015,02

2015,03

2015,04

2015,05

2015,06

2015,07

2015,08

2015,09

2014,01

2014,02

2014,03

2014,04

2014,05

2014,06

2014,07

2014,08

2014,09

2014,10

2014,11

2014,12

];

NoConcatenate

tab2:

load year,month Resident t1

where MakeDate(year,month,1)>addyears(addmonths(Today(1),-1),-1) and MakeDate(year,month,1)<addmonths(Today(1),-1);

malini_qlikview
Contributor II

Re: last 12 months extraction in script

Hi,

Please try the below, use can use the for loop either loading data from QVD or SQL,

For i=1 to 12

Target:

Load

Year,

Month,

MonthName(Addmonths(Today(),-$(i))) AS Month_Loaded

Resident Source

where

Year = Year(Addmonths(Today(),-$(i))) and Month=Num(Month(Addmonths(Today(),-$(i))));

Next

Community Browser