topic Very tricky ... in QlikView

Very tricky ...

Tue, 14 Feb 2012 17:02:11 GMT

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

Tue, 14 Feb 2012 17:23:24 GMT

could you upload some sample info?

Very tricky ...

Tue, 14 Feb 2012 18:46:14 GMT

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.

Very tricky ...

Tue, 14 Feb 2012 19:10:35 GMT

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

rbecher — Tue, 14 Feb 2012 20:18:01 GMT

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?

Re: Very tricky ...

rbecher — Wed, 15 Feb 2012 09:34:44 GMT

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

- Ralf

Very tricky ...

Wed, 15 Feb 2012 13:00:37 GMT

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

rbecher — Wed, 15 Feb 2012 13:04:30 GMT

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

Very tricky ...

Wed, 15 Feb 2012 13:07:58 GMT

Ralf, i killed the load.

CPU was 100% memory 50GB.

i will load again...

Very tricky ...

Wed, 15 Feb 2012 13:10:47 GMT

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

i will check the data now.

Very tricky ...

Wed, 15 Feb 2012 16:21:55 GMT

Ralf, it works like wonder.

Thanks !

Very tricky ...

rbecher — Wed, 15 Feb 2012 16:36:08 GMT

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

Very tricky ...

Wed, 15 Feb 2012 16:54:37 GMT

Yes. also put your IQ to test !