Looking for a formula re start / end dates

[anthony_kinsell]

Hi,

I'm trying to work out a formula to calculate the vacant days on a given apartment. I have all the data I need (I think) in Qlikview - see table below:

UnitDesc	AptNo	AptSeq	AptSeq2	TenNum	TenStartDate	TenStartDate1	TenEndDate	TenEndDate1	Vacant Days
Apartment 1	1	01/10	10	110	13/09/2012	41165	12/10/2013	41559
Apartment 1	1	01/11	11	111	13/10/2013	41560	12/10/2014	41924	0
Apartment 2	2	02/12	12	212	11/09/2012	41163	29/08/2013	41515
Apartment 2	2	02/13	13	213	01/09/2013	41518	31/08/2014	41882	2
Apartment 3	3	03/12	12	312	01/07/2012	41091	30/06/2013	41455
Apartment 3	3	03/13	13	313	01/07/2013	41456	30/09/2013	41547	0
Apartment 3	3	03/14	14	314	01/10/2013	41548	30/09/2014	41912	0
Apartment 4	4	04/11	11	411	31/08/2012	41152	30/08/2013	41516
Apartment 4	4	04/12	12	412	31/08/2013	41517	30/08/2014	41881	0
Apartment 5	5	05/13	13	513	06/12/2012	41249	05/12/2013	41613
Apartment 5	5	05/14	14	514	06/12/2013	41614	05/12/2014	41978	0
Apartment 10	10	06/14	14	1014	06/10/2012	41188	05/10/2013	41552
Apartment 10	10	06/15	15	1015	06/10/2013	41553	05/10/2014	41917	0
Apartment 12	12	07/11	11	1211	09/09/2012	41161	08/09/2013	41525
Apartment 12	12	07/12	12	1212	09/09/2013	41526	31/05/2014	41790	0

So basically what I'm trying to do is get a formula that will take the end date of 1 tenant from the start date of the next tenant. For example above on line 3 a tenant has moved out on the 29/08/13 & the new tenant moves in on the 01/09/13 (see line 4) therefore the vacant days is 2 i.e. the 30/08 & 31/08. The above is just a sample of the data - this will be done for over 50 apartments over a range of time. The vacant days above were just calculated in excel not in Qlikview.

Anyone got any idea how to do this?

[MK_QSL]

If you can upload sample file with only few data, better to answer, as from above script, it's difficult to work.

[anthony_kinsell]

Manish,

See attached - appreciate the help.

The data should show the correct number of turnovers - i.e. tenants leaving or renewing. I have commented out what I tried to do based on your previous e-mails as it's not giving the correct results - obviously I'm doing something wrong.

Anthony

[anthony_kinsell]

Manish

This may be a better qvw file to use as it lets you see the other data I am importing - I have just commented all out to only load the data we are looking at.

Anthony

[anthony_kinsell]

Can nobody tell me how this can be done???