12 Replies Latest reply: Feb 15, 2012 11:54 AM by Nick Gan

Very tricky ...

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?

• Very tricky ...

could you upload some sample info?

• Very tricky ...

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

Thanks.

• Very tricky ...

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?

• Re: Very tricky ...

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:

ComplaintID, Days, Date

C00001, 44, 2011-01-27

C00002, 20, 2011-02-02

C00003, 60, 2011-02-03

];

Report:

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?

• Re: Very tricky ...

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

- Ralf

• Very tricky ...

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" ?

• Very tricky ...

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

• Very tricky ...

CPU was 100% memory 50GB.

• Very tricky ...

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

i will check the data now.

• Very tricky ...

Ralf, it works like wonder.

Thanks !

• Very tricky ...

Great! A good example how powerful the QV script language is..

• Very tricky ...

Yes. also put your IQ to test !