topic Re: Calculate the days difference between a row and its previous in QlikView

Calculate the days difference between a row and its previous

Fri, 26 Jul 2013 14:20:11 GMT

Hi there,

I have a list of orders, the status they changed into and the date of the stauts change. I want to calculate the days that each order stayed in each status.

In other words the last column in the table below (the one with the red header text).

Is it better to do it in load time or in a straigh table for example using set analysis?

Thank you all in advance.

Order Id	Date	Status Description	# of Days is Status
1	15/7/2013	Approved	1
1	16/7/2013	Awaiting	9
1	25/7/2013	Processing	0
1	25/7/2013	Finished	0
2	15/7/2013	Approved	6
2	21/7/2013	Awaiting	4
2	25/7/2013	Processing	0
2	25/7/2013	Finished	0
3	16/7/2013	Approved	5
3	21/7/2013	Awaiting	4
3	25/7/2013	Processing	2
3	27/7/2013	Finished	0
4	10/7/2013	Approved	0
4	10/7/2013	Awaiting	15
4	25/7/2013	Processing	1
4	26/7/2013	Finished	0
5	25/6/2013	Approved	15
5	10/7/2013	Awaiting	7
5	17/7/2013	Processing	4
5	21/7/2013	Finished	0

Re: Calculate the days difference between a row and its previous

Gysbert_Wassenaar — Fri, 26 Jul 2013 14:41:20 GMT

See attached example.

Re: Calculate the days difference between a row and its previous

jsakalis — Fri, 26 Jul 2013 14:47:44 GMT

I had a similar use case and found that doing it in the script made sense for me for performance and flexibility reasons.

Performance - I was dealing with about 200 million records, so doing the calculations up front vs. doing calculations on the fly was the better route

Flexibility - Was able to use results in multiple objects in my dashboards without repeating expressions. Also was able to create buckets of days ex. 1-5 days, 6-10, etc. using Class() . So you can ask the question: How many orders are waiting 6-10 days to be processed.