Open Work Orders Between Two Date Fields

[#1]

So I am trying to create a visualization to show the count of open work orders on any given day. I have a table called OPS_WR with the date fields DATE_REQUESTED and DATE_COMPLETED, requested being the date a work order was started and completed being the finished date.

I want to be able to determine, based upon whatever date my filters are on, what the count of open work orders was on a specific day.

I saw the creating reference dates for intervals post, but my supervisor wants to try and avoid using that as it generates all possible dates for every record. I was hoping for an expression I can use in a visualization, possibly utilizing variables using DATE_REQUESTED and DATE_COMPLETED.

Any help would be much appreciated! Additionally, I have a Master Calendar for each date, and a Canonical Date calendar configured for Fiscal Year (ours starts in July).

EDIT: We initially want to create two visualizations, one using year as a dimension and one using month, would it be best to average open work orders? Because there can be an order open on June 1 and closed June 3, and another opened May 31 and closed June 3, so what would be the best way to represent that? Can it be done cleanly?

EDIT2: Additionally, if it is easier, we could just do it where we only want to know the number of open work orders on the first of the month only, so how could I show that?

Message was edited by: Casey McDonald

[Josh_Good]

Hi Casey,

To do this you should use the IntervalMatch function.  This function (when used right) will build out a data model that maps when all your POs were active.  Below is a simple script example that I use to remind me how to use this function.  Note, I'm using inline loads so the script will run independently, you will need to adjust the script to fit your specific data.

Note this will only load data that exist vs all possible data points.  Calculating for the visualisation is best practice.

-Josh

Qlik

TabA:
LOAD * INLINE [
Day
1
2
3
4
5
6
7
8
9
10
];

TabB:
LOAD * INLINE [

OPS_WR, DATE_REQUESTED, DATE_COMPLETED

1, 2, 6
2, 3, 7
3, 1, 4
4, 2, 10
];

//INNER JOIN(TabB)
INTERVALMATCH(Day)
LOAD DISTINCT
DATE_REQUESTED,
DATE_COMPLETED
RESIDENT
TabB;

[#3]

In the script, for the designations "1,2,3" under 'TabA" and the "1,2,6" under "TabB" I'm confused how that works. Do I have to enter in every record and its corresponding date? We have some million records, each with date requested and date completed.

[Josh_Good]

Hi Casey,

In my script example, I'm using an inline load.  An inline load is where you write out the data for the entire table in the script.  I gave it to you this way so you could have something that work 100% self-contained as an example. You would NOT do this for a real app.  You ultimate script will reference source tables. You definitely would not write out all the dates in your script.

"TabA" is my script is your calendar table

"TabB" is my script is your table with work orders in them.

So your script will look something like below.

Does that make sense to you?

-Josh

Qlik

Calendar:
LOAD * INLINE [
...

Date

...

From

...

;

WorkOrders:
LOAD

...

OPS_WR,

DATE_REQUESTED,

DATE_COMPLETED,

....

From

....;

INTERVALMATCH(Date)
LOAD DISTINCT
DATE_REQUESTED,
DATE_COMPLETED
RESIDENT
WorkOrders;

[#5]

How would I apply this in an expression for a visualization?

[Josh_Good]

Hi Casey,

Per my previous post doing this in a visualisation is not a good idea.  It will be easier to maintain, perform better and be a better user experience to do this in the script.  Once you have done it in the script you that data will exist in the data model so it will be trivial to create charts. 

For example, Date as the dimension and Count(OPS_WS) as your measure would should you the number of open work orders by day.

This approach will not create all possible dates for all work orders (your supervisor's concern).  This approach will be highly inefficient and performant.

-Josh

Qlik

[#7]

Thank you! I will test this and see what happens.

I received updated changes to the request, to possibly show all open work orders on just the first of the month. Ultimately, my audience isn't interested in seeing the open work orders by day. They want to see by month and by year. Day may be down the road, same with week, but as it stands they are looking for Years, and Months.

Can that still be obtained using the inline load?

EDIT: Also, some work orders have no completed date, signifying a currently open work order. We want to include those numbers as well, will this also work for that?

[Josh_Good]

‌Hi Casey,

I wouldn't change the data model based on the new requirements. If you can answer the question at the day level then it is possible to answer the month and year level question by filtering in the UI.  This approach also doesn't limit you from answering day level questions when (not if) they come up.

-Josh

Qlik

[#9]

I had to shelve this for a day or two, but now that I'm tryingto implement the script you provided above, I cannot seem to get it to work within QLIK. I implemented the following code:

Calendar:

LOAD * INLINE [

Date

DATE_REQUESTED,

DATE_COMPLETED

];

WorkRequests:

LOAD

WR_ID,

DATE_REQUESTED,

DATE_COMPLETED

Resident V_QLIK_OPS_WR;

INTERVALMATCH(Date)

LOAD DISTINCT

DATE_REQUESTED,

DATE_COMPLETED

RESIDENT V_QLIK_OPS_WR;

But all it does it create a mess of synthetic keys and tables. Did I do something wrong?

[Josh_Good]

‌ Hi Casey,

You should not be doing an inline load. I used that just as an example. I recommend you load your own master cakendar. If you are unsure how to create a master calendar there are several good example on Community.

Also don't worry about synthe keys when doing an intrrval match. This is normal. There is Qlik-lore that says synthetic keys are bad but that this untrue. The reality is when you get one you should just pause and confirm it makes sense. Synthetic Keys

-Josh

Qlik