Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tcullinane
Creator II
Creator II

Trend of issues remaining unsolved

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).

1 Reply
tcullinane
Creator II
Creator II
Author

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