Create a Calendar to return items between status date changes

[Qlik_ULG]

Hi all,

I'm trying to find a calendar solution which will enable any status date filter selections to include all items in a given status on that date.

In essence my data model is based on a list of tasks, each with a unique task ID as the primary key. These tasks have the following date attributes assigned to them: Start_Date, Analysis_Date, Implemented_Date and Closure_Date.

These dates correspond to the status of the task (Started, In Analysis, Implemented, Closed), although there is no status data field within the data set.

The standard calendars have been built on these dates, e.g. selecting Jun 2019 as the Start_MonthName, will list all tasks started in that month.

However, what I'm trying to do is create a calendar which will include all tasks that were in an open status at a given time. For example, a task has a Start_Date of 12-Apr-2019 and an Analysis_Date of 23-Jun-2019. If selecting May 2019 in this calendar, it should include this task in selected data, as it was still in an open status at the time. Currently, the standard Start_Date calendar will only include this task when Apr 2019 is selected.

In summary selecting a given Start_Date in this new calendar would return all tasks that were post Start_Date, but before Analysis_Date. I would apply the same to the subsequent status dates.

Does the solution require creating values for the dates in between the status change dates?

Thanks in advance.

[petter]

Your last question first:
You can have a solution where you create the datavalues in between the "milestones". It is rather straightforward and is often used. Then you create flags to indicate the statuses. Drawback is that if you have 2 million tasks and each task have an average of 100 days extent then the helper table (calendar selection table) you create will become a 200 million row table which wouldn't be very efficient memory-wise and on top would be slow both in generating it in your load script and also during calculations.

Conclusion: For smaller datasets with shorter timespans this is not a problem - so this solution might work in a lot of cases.

The efficient solution for larger datasets:

Create a data island calendar for user selections and use set expressions to filter the data using the selected value(s) from the data island calendar.


I can give you a sketch of this approach shortly - just give me an hour or two when I have some spare time to elaborate and I'll get back to you....

[petter]

Here is a sketch of how a solution with data islands can be constructed:

This is a rough sketch and an implementation will need adjustments to work properly. For instance it would be necessary to let undeterminded dates be populated with a date value far into the future like 2999-01-01 or something like that then the logic would be working without becoming overcomplicated...

[Akhil_Reddy · Accepted solution]

CHECK THE ATTACHED FILE ONCE.

[petter]

CORRECTION:

As I stated above the expression might not be correct and it isn't. I created a sample application in QlikView to illustrate how it works where correct set expressions are used.

A correct set expression should look like this:

{<RowID={"=($(=iSP)<=$(=Num(Min(iDATE))) AND $(=iEP)>=$(=Num(Max(iDATE))))"}>}

I have put that into a variable and used the variable in the particular expression(s) in a chart...

[petter · Accepted solution]

Here is a sample application to play around with to see how a more "Dynamic Interval Match" can be done by combining various features of QlikView;  Set Expressions, Data Islands, $-sign expansion with variables.

The regular IntervalMatch can be too memory consuming and thus also calculation intensive with larger data sets. That's why this approach can make more sense with larger or big datasets.

[Qlik_ULG]

Thank you both for your assistance with this. Confirming that these have produced the desired result.

The data set in this case is relatively small, so memory issues are not a constraint.

Much appreciated.