Occupancy rate for real estate

[#1]

Dear experts,

I have a problem with calculating occupancy rate for a real estate.

I have different houses which have multiple start and end dates for renting periods.

House_ID	Start_date	End_date
1	5.5.2006	27.1.2010
1	1.4.2010	31.10.2010
1	21.12.2010	30.11.2011
1	1.12.2011	31.7.2012
1	15.12.2012	-
2	10.10.2006	28.2.2013
2	1.4.2013	1.9.2013
2	1.2.2014	20.10.2014
...	...	...

Now I need to calculate the occupancy rate of the flat per every year. (Which means the number of days house hasn't been rented in a year)

Any ideas how to do this?

Thank you!

-Mikael

[francoiscave]

Hi MiKael,

The Networkdays() function would be useful.

See the description and the link below:

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9 

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

http://community.qlik.com/message/407267#407267

Have fun with QV,

François

[consenit · Accepted solution]

Hi there.

See the document attached. I generated all "missing" dates between the start/end dates, learned this technique from the excellent "Generating missing data in QlikView" document by Henric Cronström available in the Resources section.

The only catch is I had to hard-code the current date for record 5 in the INLINE statement because actual start/end date values are needed, you can fill those up using IF(), ISNULL() and TODAY() in your script.

Kind regards,

Ernesto.

[#4]

Exactly what I was looking for. Thank you Ernesto and happy holidays!