Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Open Work Orders Between Two Date Fields

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

10 Replies
Not applicable
Author

Ah okay. I already have Master Calendars and a Canonical Date Calendar for my data model. I should only have to implement this:

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;

Portion of the script then?