Conditional filter between two dates

[#1]

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

[MK_QSL · Accepted solution]

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;

[#3]

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