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