Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
neetha_p
Honored Contributor

Display tickets by age


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

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Display tickets by age

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

View solution in original post

30 Replies
Highlighted
vgutkovsky
Honored Contributor II

Re: Display tickets by age

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

Highlighted

Re: Display tickets by age

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

Highlighted
Partner
Partner

Re: Display tickets by age

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

Highlighted
neetha_p
Honored Contributor

Re: Display tickets by age

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

Highlighted

Re: Display tickets by age

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

View solution in original post

Highlighted
neetha_p
Honored Contributor

Re: Display tickets by age

Hi Henric,

i am using below script as per your suggestion:

 

If

(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

Highlighted
vgutkovsky
Honored Contributor II

Re: Display tickets by age

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

Highlighted
neetha_p
Honored Contributor

Re: Display tickets by age

Hi Vlad,

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

Regards

Nitha

Highlighted
vgutkovsky
Honored Contributor II

Re: Display tickets by age

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

Thanks,

Vlad