Looking for a formula re start / end dates - Qlik Community

anthony_kinsell · ‎2014-04-28

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?

tresesco · ‎2014-04-28

Using straight table and function below()/above(), can take you to goal. Try to share your sample app.

MK_QSL · ‎2014-04-28

Temp:

Load *, RowNo() as NO Inline

[

UnitDesc, AptNo, AptSeq, AptSeq2, TenNum, TenStartDate, TenStartDate1, TenEndDate, TenEndDate1

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

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

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

Apartment 3, 3, 03/14, 14, 314, 01/10/2013, 41548, 30/09/2014, 41912

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

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

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

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

];

NoConcatenate

Final:

Load

*,

IF(AptNo=Previous(AptNo),TenStartDate-Previous(TenEndDate)-1) as VacantDays

Resident Temp

order By AptNo, TenNum;

anthony_kinsell · ‎2014-04-28

Hi tresesco,

I can see your thinking with the above function, however I would only want to do that if the Apt no was the same - could this be done in an IF formula maybe?

anthony_kinsell · ‎2014-04-28

Sorry Manish, not quite following what you mean there?

MK_QSL · ‎2014-04-28

I have created a script for you... .from script VacantDays is the field you required.

anthony_kinsell · ‎2014-04-28

Think this is where the confusion lies – you have created a script from my straight table data.

Perhaps what is needed is something similar on the actual script I have used rather than on the data?

MK_QSL · ‎2014-04-28

What is your actual script?

anthony_kinsell · ‎2014-04-28

There are various tabs for different items - the temp for the above is as follows:

TurnoverTemp:

LOAD

UnitRef,

UnitDesc,

Right(UnitDesc,2) as AptNo,

UnitType,

TenantRef,

Right(TenantRef,5) as AptSeq,

Right(TenantRef,2) as AptSeq2,

TenantName,

TenStart as TenStartDate,

Num(TenStart) as TenStartDate1,

TenEnd as TenEndDate,

Num(TenEnd) as TenEndDate1,

date(monthstart(TenEnd), 'MMM-YYYY') AS YearMonth

FROM

$(vLoadPath)\Block_B_Turnover_Analysis.xlsx

(ooxml, embedded labels, table is Sheet1);

That is then added to a data table:

Concatenate (Data)

LOAD Distinct

UnitRef,

UnitDesc,

AptNo,

UnitType,

TenantRef,

AptSeq,

AptSeq2,

TenantName,

TenStartDate,

TenStartDate1,

TenEndDate,

TenEndDate1,

YearMonth,

AptNo & AptSeq2 as TenNum

Resident TurnoverTemp;

Drop Table IncomeTemp;

Drop Table ERV;

DROP Table TurnoverTemp;

anthony_kinsell · ‎2014-04-28

Manish,

I've commented out all the other data to leave just the turnover temp & data tabs - script now looks like this:

TurnoverTemp:

LOAD

UnitRef,

UnitDesc,

Right(UnitDesc,2) as AptNo,

UnitType,

TenantRef,

Right(TenantRef,5) as AptSeq,

Right(TenantRef,2) as AptSeq2,

TenantName,

TenStart as TenStartDate,

Num(TenStart) as TenStartDate1,

TenEnd as TenEndDate,

Num(TenEnd) as TenEndDate1,

date(monthstart(TenEnd), 'MMM-YYYY') AS YearMonth,

Right(UnitDesc,2) & Right(TenantRef,2) as TenNum

FROM

$(vLoadPath)\Block_B_Turnover_Analysis.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD Distinct

UnitRef,

UnitDesc,

AptNo,

UnitType,

TenantRef,

AptSeq,

AptSeq2,

TenantName,

TenStartDate,

TenStartDate1,

TenEndDate,

TenEndDate1,

YearMonth,

TenNum,

If(AptNo = Previous(AptNo), TenStartDate1 - (Previous(TenEndDate1)-1)) as VacantDays

Resident TurnoverTemp

Order By AptNo, TenNum;

//Drop Table IncomeTemp;

//

//Drop Table ERV;

DROP Table TurnoverTemp;

But it's still not giving me the correct answer - it doesn't seem to be ordering the data by AptNo & then by TenNum. It's also doubled the number of end dates - i.e. it's gone from 110 to 220.

Results attached