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

[ChiragPradhan]

Can you post a row of your data to see the schema? This is what I based my query on and it seems to be working.

LOAD * Inline [

CallID, CallOpened, CallClosed

1,'01/01/2014 05:30:29',

2,'01/02/2014 06:30:30','02/02/2014 07:30:30'

3,30/06/2014 01:02;30

4,29/06/2014 02:03:40

5,01/06/2014 04:30:06

6,19/06/2014 05:06:45

];

[ChiragPradhan]

Also there was a typo in my earlier script. here is the correct one

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

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

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

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

[Anonymous]

Now two values 'Something else happened'  and  'Call is closed'  values are populated in list box,

it seems if there are no tickets in that range, value is not populated in list box,but i want all values populated irrespective of tickets open status or not for that range.

[vgutkovsky]

Interesting, wasn't expecting the "Something else happened" value. Make sure you are using the correct MM/DD or DD/MM alternative. Which one is it, by the way? Also, is [Call Opened - Date/Time] always populated? Could it be that you have null values in there?

Vlad

[Anonymous]

Hi Vlad,

[Call Opened - Date/Time] is always populated and it looks like if the tickets for a range doesn't have open tickets that value is not getting populated in OpenByAge list box and i want to populate all values in listbox irrespective of no tickets open in that range.

Regards

Neetha

[vgutkovsky]

I don't understand. I thought that the statement we've been developing was being run into the same table that has tickets. So TicketID and OpenByAge are in the same table. If that's not the case, then this matter has just gotten a lot more confusing, and I'm going to need to ask you to post a reduced copy of your app.

Regards,

Vlad

[Anonymous]

Hi Vlad,

Its are in the same table and i can't upload the copy.

[vgutkovsky]

In that case, I'm not sure what you mean by "range" in your previous comment. The OpenByAge field will be populated per row. That means it could look like this:

P1

P1

null

P3

...

If you create a list box of this field, it will just display unique values of the field, like all QlikView list boxes.

Vlad

[Anonymous]

Hi Vlad,

I got solution for display all values in list box using data island.

But i have issue below script ,its taking minutes and hours as days. 

If(Len(Trim([Call Closed - Date/Time])) = 0 ,
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'mm'))<=90, 'P1',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'mm'))<=240, 'P2',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'),'hh'))<=8, 'P3',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P4',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P5',
If((interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P6','P7'))))))) as OpenByAge,

Please advise what could be the problem.

Regards

Neetha

[Anonymous]

Hi Vlad,

Got the solution

If

(Len(Trim([Call Closed - Date/Time])) = 0 ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'mm')) < Interval(Interval#(90, 'mm')), 'P1' ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'mm')) < Interval(Interval#(240, 'mm')), 'P2' ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'hh')) < Interval(Interval#(8, 'hh')), 'P3' ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'DD')) < Interval(Interval#(1, 'DD')), 'P4' ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'DD')) < Interval(Interval#(5, 'DD')), 'P5' ,
If((Interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date([Call Opened - Date/Time],'DD-MM-YYYY hh:mm:ss'), 'DD')) < Interval(Interval#(20, 'DD')), 'P6' ,'P7' ))))))) AS OpenByAge,

Thanks

Neetha