restrict last 6months data in the below extraction

[mahitham]

Hi Experts,

Can any one please help me to restrict only last 6 Months data in the below extraction.

Load

DepartmentName,

Departmentid,

Date,

Salary

From ....

Here Date is in DD/MM/YYYY hh:mm:ss format.

Please help me on this.

Thanks in advance.

[chinnuchinni]

try this:

let Date1 = date(today());
let Date2 = date(MonthStart(today(),-6));

Load

DepartmentName,

Departmentid,

Date,

Salary

From ....

where date(Date_field)>=date('$(Date1)') and date(Date_field)<=date('$(Date2)');

[Anonymous]

you dont have to care about hh:mm:ss.

Load

DepartmentName,

Departmentid,

Date,

Salary

where date(Date) =< addmonths(today(),-6);

[bharat19s · Accepted solution]

Get the 6 month back date in variable & use the same to loading data in where condition:

e.g.

LET vToday = Date(AddMonths(Today(),-6),'DD/MM/YYYY hh:mm:ss');

LET vToday_1 = Date(Today(),'DD/MM/YYYY hh:mm:ss');

Load

DepartmentName,

Departmentid,

your_Date,

Salary

From   XYZ where your_Date >= '$(vToday)' and  your_Date <= '$(vToday_1)';

[Gabriel]

Hi,

What I would do is to restrict your KEY date in you fact table. You will have to format you KeyDate as well

LET vLast_6_Months =  TODAY() - 180;

Fact:

LOAD * FROM source

WHERE KeyDate <= DATE($(vLast_6_Months),'DD/MM/YYYY');