Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?