Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like:
Load ....
Resident ....
Where year = Year(MonthStart(AddMonths(Today(),-12))) and month = month(MonthStart(AddMonths(Today(),-12))) ;
let me know
Let varMaxDate = Num(MakeDate(2014,9,1));
Load
inyear(DateField, Monthstart($(varMaxDate)),-1) as Last12months
from datasoruce;
Try something like
Tab:
Load *
From <datasource>
where date >= Addyear(MonthEnd(Today()), -1);
Resident
Where year = Year(MonthStart(AddMonths(Today(),-12))) and month = month(MonthStart(AddMonths(Today(),-12))) <Tablename> ;
it might help u.
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
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);
Please try the below, use can use the for loop either loading data from QVD or SQL,
For i=1 to 12
Target:
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
