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