Peek / Previous Help!

[#1]

Dear all please help!

I have been struggling for the past few days with a problem.

Undoubtedly that the answer must be very simple but I surely cannot see it at all

I have an SQL database with Tickets.

Between their created date: t_created_date and their closed date: t_closed_date, these tickets go through various stages which are monitored and have their own dates: created_date.

I am interested in creating an analysis of these intermediary stages basically.

Within a chart / pivot table I was able to do this as following:

=if(Below(created_date)<>Null(),

dual(

floor(Interval(Below(created_date)-created_date))

&' days ' & hour(Interval(Below(created_date)-created_date))

& ' h ' & minute(Interval(Below(created_date)-created_date))

& ' min ' & second(Interval(Below(created_date)-created_date))

& ' sec ', Interval(Below(created_date)-created_date)),

if(Status<>'closed',

dual(

floor(Interval(today()-created_date))

&' days ' & hour(Interval(today()-created_date))

& ' h ' & minute(Interval(today()-created_date))

& ' min ' & second(Interval(today()-created_date))

& ' sec ', Interval(today()-created_date))

,dual(

floor(Interval(t_closed_date-t_created_date))

&' days ' & hour(Interval(t_closed_date-t_created_date))

& ' h ' & minute(Interval(t_closed_date-t_created_date))

& ' min ' & second(Interval(t_closed_date-t_created_date))

& ' sec ', Interval(t_closed_date-t_created_date)

)))

The function will deduct from the previous date the first one for the intermediary stages and if it's closed will deduct from the closed date the open one, if it's not closed it means that the ticket is still open and it will deduct from today's date the last date of the intermediary status.

Ticket	ID	Status_ID	created_date	Status	Time In Status
1	1	15	4/21/2010 7:34	new call	0 days 0 h 4 min 4 sec
1	8	24	4/21/2010 7:38	solution provided	397 days 0 h 13 min 0 sec
1	12792	23	5/23/2011 7:51	closed	397 days 0 h 17 min 4 sec
2	9	15	4/21/2010 7:39	new call	1 days 1 h 30 min 46 sec
2	119	24	4/22/2010 9:10	analysis	19 days 22 h 11 min 10 sec
2	482	24	5/12/2010 7:21	re-creation & doc	0 days 0 h 4 min 20 sec
2	483	24	5/12/2010 7:25	UAT	0 days 0 h 11 min 45 sec
2	484	23	5/12/2010 7:37	closed	20 days 23 h 58 min 1 sec
3	21	15	4/21/2010 8:03	new call	0 days 0 h 2 min 57 sec
3	27	24	4/21/2010 8:06	req additional info	1 days 2 h 34 min 9 sec
3	124	24	4/22/2010 10:40	solution provided	127 days 2 h 22 min 5 sec
3	4208	23	8/27/2010 13:02	closed	128 days 4 h 59 min 11 sec
4	28	15	4/21/2010 8:06	new call	1 days 1 h 5 min 59 sec
4	120	24	4/22/2010 9:12	analysis	45 days 23 h 47 min 47 sec
4	1072	9	6/7/2010 9:00	req additional info	365 days 0 h 5 min 30 sec
4	13522	9	6/7/2011 9:06	solution provided	217 days 6 h 3 min 22 sec
4	27816	23	1/10/2012 15:09	closed	629 days 7 h 2 min 38 sec

The problem is that I need the Time in Status to be calculated in script as I need to use it in various other charts.

I've tried defining in script a function: Interval(previous(created_date)-created_date) as Timestamp

but I have the following result (after adding Timestamp to my original chart to see how it looks)

Ticket	ID	Status_ID	Created_Date	Status	Time In Status	Timestamp
1	1	15	4/21/2010 7:34	new call	0 days 0 h 4 min 4 sec	-
1	8	24	4/21/2010 7:38	solution provided	397 days 0 h 13 min 0 sec	0 days 0 h 4 min 4 sec
1	12792	23	5/23/2011 7:51	closed	397 days 0 h 17 min 4 sec	397 days 0 h 13 min 0 sec
2	9	15	4/21/2010 7:39	new call	1 days 1 h 30 min 46 sec
2	119	24	4/22/2010 9:10	analysis	19 days 22 h 11 min 10 sec	1 days 1 h 30 min 46 sec
2	482	24	5/12/2010 7:21	re-creation & doc	0 days 0 h 4 min 20 sec	19 days 22 h 11 min 10 sec
2	483	24	5/12/2010 7:25	UAT	0 days 0 h 11 min 45 sec	0 days 0 h 4 min 20 sec
2	484	23	5/12/2010 7:37	closed	20 days 23 h 58 min 1 sec	0 days 0 h 11 min 45 sec
3	21	15	4/21/2010 8:03	new call	0 days 0 h 2 min 57 sec	20 days 23 h 58 min 1 sec
3	27	24	4/21/2010 8:06	req additional info	1 days 2 h 34 min 9 sec	0 days 0 h 2 min 57 sec
3	124	24	4/22/2010 10:40	solution provided	127 days 2 h 22 min 5 sec	1 days 2 h 34 min 9 sec
3	4208	23	8/27/2010 13:02	closed	128 days 4 h 59 min 11 sec	127 days 2 h 22 min 5 sec
4	120	24	4/22/2010 9:12	analysis	45 days 23 h 47 min 47 sec	1 days 1 h 5 min 59 sec
4	1072	9	6/7/2010 9:00	req additional info	365 days 0 h 5 min 30 sec	45 days 23 h 47 min 47 sec
4	13522	9	6/7/2011 9:06	solution provided	217 days 6 h 3 min 22 sec	365 days 0 h 5 min 30 sec
4	27816	23	1/10/2012 15:09	closed	629 days 7 h 2 min 38 sec	217 days 6 h 3 min 22 sec

I understand that it is normal as previous will return the 1st row as NULL, however everything is lowered with 1 and I am clueless on how to make it right.

Any thoughts?

As you can see also, I didn't calculate the Below(created_date)<>Null() from my chart function in script either ... how could I do this also?

Thank you all,

Alex

[#2]

Hello Steve, welcome back!

I actually managed to work out the solution you gave me and thank you for this

Speaking of elegant solutions, I actually have a proposal for you: as I am fairly new to Qlikview and still struggling to make sense out of many things I would require a little bit of help, especially on the optimization part of my project. I cannot qualify it as being an extensive one truth be told .. I presume that for a seasoned developer is piece of cake and what I have done in 2 months he or she would have done it in 2 weeks, maybe less.

With this in mind, I would need someone to check a bit the code/tables and help me optimize it/them (or point me on the right track, as I am aware of the fact that the solutions I chose for various tasks were the "lumber Jack's" ones )

I still have 2 modules to make but I am estimating that by Tuesday they will be done .. hopefully ..

I also have 3 more questions related to them, but they should be done on the task itself.

If you would be interested, please drop me a line at raammaam@gmail.com.

I am estimating that overall, this would not take more than 3 hours.

Thank you,

Alex

P.S. The trouble will be taken into consideration of course..

[#3]

Did my solution work then?  I hope so, if it was if you can mark it as correct/helpful I'd appreciate it.

Can you let me know what additional assistance you need?  It may be best to post it as a separate discussion, that way if I cannot assist, somebody else may be able to.