So... if I understand this correct - you want a line chart that shows by day/month, the number of cases opened on that day/month and how long that case took to be resolved?
I wonder if there is a better way to visualize this data. Basically, you want to answer the question of: "how long does it take to resolve a ticket?" and "are we getting better at resolving tickets over time?"
Is that correct?
if I understand it right I would create "synthetical" records for each compliant in each month with calculated days per month (see attachment also):
LOAD * INLINE [
ComplaintID, Days, Date
C00001, 44, 2011-01-27
C00002, 20, 2011-02-02
C00003, 60, 2011-02-03
LOAD ComplaintID, Days, DateBegin, DateEnd,
MonthName(AddMonths(DateBegin, iterno()-1)) as Month,
NumMin(DateEnd +1, MonthEnd(AddMonths(DateBegin, iterno()-1))) - NumMax(DateBegin, MonthStart(AddMonths(DateBegin, iterno()-1))) as DaysPerMonth
While MonthEnd(DateEnd) >= MonthEnd(AddMonths(DateBegin, iterno()-1));
Date(Date, 'YYYY-MM-DD') as DateBegin,
Date(Date(Date, 'YYYY-MM-DD') +Days -1) as DateEnd
Drop Table Compliants;
BTW I wonder why the calculated days per month are float values. Maybe this is a bug?
Complaints.qvw 156.8 K