Very tricky ...

[#1]

Hi,

Really very tricky... (i think... )

Says, i'm counting the number of days for a complaint ticket resolution.

From the date it's logged into the system until it is closed.

So, i will have complaint ticket like....(also the data structure)

Case 1 - 44 days - 27/Jan/2011

Case 2 - 20 days - 2/Feb/2011

Case 3 - 60 days - 3/Feb/2011

Then, i want to make a line chart, to see some trend. for average days. basically sum(days)/ number of cases

For case 1,

Jan, count of case = 1, day=4 (because there was 31 days in Jan 2011)

Feb, count of case = 1, day = 30 (assume there was 30 days for Feb 2011)
Mar, count of case = 1, day=10

For case 2,

Jan, count of case = 1, day=20

The purpose of this is to really look at the time needed to serve a complaint.

so ultimately, to improse the service.

it doesnt really matter if we just set that a month has 30 days to make it easier.

Hope some expert will be able to help?

[#2]

could you upload some sample info?

[#3]

Hi Juan,

The structure that i'm having now is ...

Complaint ID     Days         Date

C00001                44 days    27/Jan/2011

C00002                20 days    2/Feb/2011

C00003                60 days    3/Feb/2011

Which days are precalculated already.

Thanks.

[#4]

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?

[rbecher · Accepted solution]

Hi Nicksatch,

if I understand it right I would create "synthetical" records for each compliant in each month with calculated days per month (see attachment also):

Compliants:

LOAD * INLINE [

    ComplaintID, Days, Date

    C00001, 44, 2011-01-27

    C00002, 20, 2011-02-02

    C00003, 60, 2011-02-03

];

Report:

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

LOAD ComplaintID,

Days,

Date(Date, 'YYYY-MM-DD') as DateBegin,

Date(Date(Date, 'YYYY-MM-DD') +Days -1) as DateEnd

resident Compliants;

Drop Table Compliants;

- Ralf

BTW I wonder why the calculated days per month are float values. Maybe this is a bug?

[rbecher]

Now a better version w/o the float values. It wasn't a bug..

- Ralf

[#7]

Ralf,

Thanks for the example, i'm testing it in my application.

Seems like the "while" is generating the needed rows.

and now i'm testing it with one month's data on the server.

i can see CPU to 100%  and it's still running now.

is there alternative than using "while" ?

[rbecher]

A join with a calender table could probably do it..

[#9]

Ralf, i killed the load.

CPU was 100% memory 50GB.

i will load again...

[#10]

ok, it's good like. i moved the tab to the last.

i will check the data now.