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

[Anonymous]

Hi Vlad,

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

Regards

Nitha

[vgutkovsky]

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

[Anonymous]

Hi Vlad,

No Success

Regards

Nitha

[ChiragPradhan]

Like this?

[vgutkovsky]

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

[Anonymous]

Tried Both!

[Anonymous]

Hi Chirag,

can't open external doc.

Please can you write script here

Regards

Nitha

[ChiragPradhan]

=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'))))

[vgutkovsky]

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

[Anonymous]

Only P4 is getting populated in listed box