topic Re: last 12 months extraction in script in QlikView

last 12 months extraction in script

grajmca_sgp123 — Mon, 07 Sep 2015 09:30:42 GMT

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

Re: last 12 months extraction in script

alexandros17 — Mon, 07 Sep 2015 09:36:51 GMT

Something like:

Load ....

Resident ....

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

let me know

Re: last 12 months extraction in script

sunilkumarqv — Mon, 07 Sep 2015 09:49:12 GMT

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

Load

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

from datasoruce;

Re: last 12 months extraction in script

senpradip007 — Mon, 07 Sep 2015 09:52:44 GMT

Try something like

Tab:

Load *

From <datasource>

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

Re: last 12 months extraction in script

Mon, 07 Sep 2015 10:04:05 GMT

Hi,

Load *

Resident

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

it might help u.

Re: last 12 months extraction in script

Anonymous — Mon, 07 Sep 2015 10:24:21 GMT

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

Re: last 12 months extraction in script

sasiparupudi1 — Mon, 07 Sep 2015 10:56:49 GMT

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

Re: last 12 months extraction in script

malini_qlikview — Mon, 07 Sep 2015 12:12:14 GMT

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