Count active and completed work between dates in load statement

ttmaroney — Tue, 19 Apr 2016 13:24:59 GMT

I need help, head hurts-

We have metric (Weeks of Work) which is calculated by dividing the sum of active work requests by the sum of work requests resolved in the prior week. This tells us how many weeks of work we have in total and in each area. It’s always reported as of Wednesdays at 8am, is a very manual process, which I’m hoping to accomplish in QlikView.

I have the following dimensions loaded from an Excel workbook: [Week of] is the date the metric is reported, always a Wednesday morning at 8 am, and [Prior Week Start] is calculated by subtracting 7 days from [Week of]. It only loads where [Week of] is less than today. So the [Week of] dates will be 4/13/16 at 8AM, 4/6/16 at 8AM, 3/30/16 at 8AM, … you get the idea. As of tomorrow at 8, will include 4/20/16 at 8am.

From a SQL database, I also load: [Ticket Number], and its [Open Date], and [Resolve Date].

I need three calculations (I need help- lots and lots of help, with the first two):

Count the number of tickets Active (so to be counted, the ticket has to have been open prior to [week of] and either be resolved after [Week of] or be currently unresolved (this is where I get lost).
1. ?? Count({[Ticket Number] [Open Date]<[Week of] and ([Resolve Date]>[Week of] OR [Resolve Date] is null) as Active
Count the number of tickets resolved in the past week.
1. ?? Count({$<[Ticket Number], [Prior Week Start]<[Resolve Date]<[Week of]}) as Resolved
Take the answer from step 1 and divide it by the answer to step 2
1. Active/Resolved as [Weeks of Work]

Below is my syntax so far. any help would be appreciated.

WeeksOfWork:
LOAD [Week of],
[Prior Week Start]
FROM
Data\WOWweeks.xlsx
(ooxml, embedded labels, table is [WOW Weeks])
Where [Week of]< Timestamp(today(),'M-D-YYYY h:mm');

Concatenate (WeeksOfWork)
Load
[Ticket Number],
[Open Date] as zOpenDate,
[Resolve Date] as zResolveDate

// Count({[Ticket Number] [Open Date]<[Week of] and ([Resolve Date]>[Week of] OR [Resolve Date] is null as Active
// Count({$<[Ticket Number], [Prior Week Start]<[Resolve Date]<[Week of]) as Resolved
// Active/Resolved as [Weeks of Work]

Resident Tickets;

topic Count active and completed work between dates in load statement in QlikView

Count active and completed work between dates in load statement