Trend of issues remaining unsolved

tcullinane — Mon, 15 Sep 2014 16:08:25 GMT

I am trying to create a trend showing the total count of issues unsolved (so ticket count on y-axis and timeline on x-axis)

The data I have is the Created Date of the issue and the Resolved Date (if there is one).

The problem is that for the Calendar/Timeline I am only able to show the number of issues unsolved that day that were created on the same day, not the accumulated total of currently unsolved (obviously issues can be solved).

Re: Trend of issues remaining unsolved

tcullinane — Thu, 25 Sep 2014 09:30:14 GMT

I have a solution to this problem but it seems very inefficient. If anyone has a more efficient method or suggestions on how I can improve this so when the data grows it does not become unusable, I'd like to hear it. There are a few similar calculations I wish to implement but combined would make for a slow load.

This is my code in the load script:

//Create temp table
Tmp_joinWorkOrderUnresolved:
LOAD * inline [
     join_Date]
;

//for each day in calendar
FOR DateJ = peek('join_Date',0,'Calendar') to peek('join_Date',-1,'Calendar')
//join to temp table
join
Tmp_WorkOrder:
//single row table with the date and count of all workorders that have a created date before the day and resolved day after the day.
Load $(DateJ) as join_Date,
     Count(Distinct WORKORDERID) as COUNTWOUNRESOLVED
resident WorkOrder
Where CREATEDDATE<=$(DateJ) and RESOLVEDDATE>$(DateJ);

NEXT

this then gives me a table with the count for each calendar date (the calendar is made of dates from the first created workorder to the latest) so creating a chart is simple

topic Re: Trend of issues remaining unsolved in QlikView

Trend of issues remaining unsolved

Re: Trend of issues remaining unsolved