Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

restrict last 6months data in the below extraction

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.

1 Solution

Accepted Solutions
bharat19s
New Contributor II

Re: restrict last 6months data in the below extraction

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


4 Replies
chinnuchinni
Contributor III

Re: restrict last 6months data in the below extraction

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

delroekid
Contributor

Re: restrict last 6months data in the below extraction

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

Load

DepartmentName,

Departmentid,

Date,

Salary

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

bharat19s
New Contributor II

Re: restrict last 6months data in the below extraction

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
Valued Contributor II

Re: restrict last 6months data in the below extraction

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

Community Browser