topic Re: Conditional filter between two dates in QlikView

Conditional filter between two dates

Thu, 16 Apr 2015 14:39:19 GMT

Hello!

I have two fields (Assignment start date - Assignment end date) A worker is active on a certain date, if that date is between the start and end date

Is there anyway that I would be able to enter a date (preferably a specific month) which would would then show all workers active at that date

So for example if I were to enter March, it would filter down to all end dates greater than the 1st of March, and all start dates less than the 31st March

I'm sure it could be done with an IF statement, however I am relatively new to Qlik and am not sure how the fields are pulled in or where to compile the code to

Any help would be greatly appreciated

For data protection reasons I can not upload my project, but I can mock up some data if needed

Thank you

Re: Conditional filter between two dates

MK_QSL — Thu, 16 Apr 2015 16:11:36 GMT

Employee:

Load

Employee,

Date(StartDate) as StartDate,

Date(If(Len(Trim(EndDate))=0 or IsNull(EndDate),Today(),EndDate)) as EndDate

Inline

[

Employee, StartDate, EndDate

A, 01/02/2014, 10/12/2014

B, 15/01/2014, 10/10/2015

C, 01/03/2015,

D, 15/05/2014, 31/12/2014

E, 25/07/2014,

F, 12/12/2014, 10/04/2015

G, 25/08/2013, 20/12/2014

];

MinMaxDate:

Load

RangeMin(Min(StartDate),Min(EndDate)) as MinDate,

RangeMax(Max(StartDate),Max(EndDate)) as MaxDate

Resident Employee;

Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

Calendar:

Load

Date(TempDate) as Date,

Month(TempDate) as Month,

Year(TempDate) as Year,

Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear;

Load

$(vMinDate)+IterNo()-1 as TempDate

AutoGenerate 1

While $(vMinDate)+IterNo()-1 <= $(vMaxDate);

IntervalMatch(Date)

Load StartDate, EndDate Resident Employee;

Re: Conditional filter between two dates

Fri, 17 Apr 2015 10:21:01 GMT

Hey, thank you very much for the reply!

Does this all go into the script? I currently have a large script already importing the rest of the data. So would this be added to the end or would it be best to add into a new tab??

Thank you

Edit: I have had a chance to run the code and it does run perfectly, exactly as I wanted it too! I just need to figure how to translate it into interacting with my existing data set. Thank you again