Display tickets by age

[Anonymous]

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

[vgutkovsky]

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

[hic]

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

[eduardo_sommer]

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

[Anonymous]

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

[hic · Accepted solution]

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

[Anonymous]

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

[vgutkovsky]

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

[Anonymous]

Hi Vlad,

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

Regards

Nitha

[vgutkovsky]

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

Thanks,

Vlad