topic Re: Display tickets by age in QlikView

Display tickets by age

Anonymous — Mon, 30 Jun 2014 13:44:14 GMT

Hi,

Please can any one help me with below query i need to calculate age of open tickets in script.

Below is my script:

If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

please can you tell me what am i doing wrong.

Regards

Nitha

Re: Display tickets by age

vgutkovsky — Mon, 30 Jun 2014 19:58:17 GMT

I think the problem is that you're using the date formatter versus the date transformer. If your [Call Opened - Date/Time] field is in format DD-MM-YYYY, then this should return the interval between today and that date, in days:

today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))

There's actually no need to even use interval if you want the difference in days.

Regards,
Vlad

Re: Display tickets by age

hic — Mon, 30 Jun 2014 20:05:12 GMT

You have three If() functions that all demand IsNull([Call Closed - Date/Time]). Is this really what you want? This means that you only assign values if this field is NULL. It also means that the 2nd and 3rd If() are meaningless, since they always will evaluate to FALSE.

HIC

Re: Display tickets by age

eduardo_sommer — Mon, 30 Jun 2014 20:14:22 GMT

I think the problem is in the use of the IsNull function. If the field is null (as you are testing) you cannot substract it from today(). Maybe you must change to

If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

Eduardo

Re: Display tickets by age

Anonymous — Mon, 30 Jun 2014 20:20:52 GMT

Hi Henric,

sorry for mistake,below is what my script looks like:

If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

I want to calculate age of open tickets till today date when closed date column doesn't have any value.

Please advise.

Thanks

Nitha

Re: Display tickets by age

hic — Mon, 30 Jun 2014 20:25:26 GMT

Then it could be that [Call Closed - Date/Time] never is NULL. Empty field is not the same as NULL.

Try

Len(Trim([Call Closed - Date/Time]))=0

instead of IsNull().

HIC

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 09:38:49 GMT

Hi Henric,

i am using below script as per your suggestion:

(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=1, 'P1',
If(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=5, 'P2',
If(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=20, 'P3'))) as OpenByAge,

once script is loaded ,no value is shown in OpenByage listbox .

Regards

Nitha

Re: Display tickets by age

vgutkovsky — Tue, 01 Jul 2014 14:43:50 GMT

Nitha, give this a try:

if(len(trim([Call Closed - Date/Time]))>0,

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=1,

'P1',

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=5,

'P2',

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=5,

'P3'

)

) as OpenByAge

Regards,

Vlad

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 14:57:54 GMT

Hi Vlad,

Thanks ,but still its not populating data in OpenByAge list box

Regards

Nitha

Re: Display tickets by age

vgutkovsky — Tue, 01 Jul 2014 14:59:40 GMT

What's the format of [Call Opened - Date/Time]? Can you post one complete value of this field?

Thanks,

Vlad

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 15:26:26 GMT

Hi Vlad,

The value in the field id 01/05/2014 05:39:10

Regards

Nitha

Re: Display tickets by age

vgutkovsky — Tue, 01 Jul 2014 15:31:59 GMT

Ah, that would explain it. Try this (assuming it's DD/MM and not MM/DD):

if(len(trim([Call Closed - Date/Time]))>0,

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=1,

'P1',

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=5,

'P2',

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=5,

'P3'

)

) as OpenByAge

If it's MM/DD, just change the date formatting.

Regards,

Vlad

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 15:42:06 GMT

Hi Vlad,

No Success

Regards

Nitha

Re: Display tickets by age

ChiragPradhan — Tue, 01 Jul 2014 15:42:25 GMT

Like this?

Re: Display tickets by age

vgutkovsky — Tue, 01 Jul 2014 15:48:18 GMT

Your tried both 'DD/MM/YYYY hh:mm:ss' and 'MM/DD/YYYY hh:mm:ss' (not sure which one is right in your case, since your example could be either)?

Vlad

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 15:58:29 GMT

Tried Both!

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 15:59:54 GMT

Hi Chirag,

can't open external doc.

Please can you write script here

Regards

Nitha

Re: Display tickets by age

ChiragPradhan — Tue, 01 Jul 2014 16:01:22 GMT

=if(Len(Trim([CallClosed])) = 0 and interval(Date(Today(),'DD-MM-YYYY hh:m:ss')-Date([CallOpened],'DD-MM-YYYY hh:m:ss'),'DD')<=1, 'P1',

If(Len(Trim([CallClosed])) = 0 and interval(Date(Today(),'DD-MM-YYYY hh:m:ss')-Date([CallOpened],'DD-MM-YYYY hh:m:ss'),'DD')<=5, 'P2',

If(Len(Trim([CallClosed])) = 0 and interval(Date(Today(),'DD-MM-YYYY hh:m:ss')-Date([CallOpened],'DD-MM-YYYY hh:m:ss'),'DD')<=20, 'P3',

If(Len(Trim([CallClosed])) = 0, 'P4'))))

Re: Display tickets by age

vgutkovsky — Tue, 01 Jul 2014 16:05:12 GMT

OK, let's try adding some ELSEs into the IF to see where we're losing it. Try this:

if(len(trim([Call Closed - Date/Time]))>0,

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=1,

'P1',

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=5,

'P2',

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))<=20,

'P3',

if(today() - floor(date#([Call Opened - Date/Time],'DD/MM/YYYY hh:mm:ss'))>20,

'Greater than 20',

'Something else happened'

)

'Call is closed'

) as OpenByAge

Regards,

Vlad

Re: Display tickets by age

Anonymous — Tue, 01 Jul 2014 16:09:44 GMT

Only P4 is getting populated in listed box