Date ranges in multiple fields - how to/best practice

[#1]

Dear sirs,

I am building a business intelligence-qlikview on a business of renting office space in a building. (Property management) I want to calculate rented offices in m2 divided by available m2, average prices, estimats and forecasts aso.

My source database holds a lot af tables: the important ones are like this

Tab_BuildingSection:
BuildingSectionID, //for example 1st floor, 2nd floor aso.
BuildingID,
BuildingSectionArea, // number of square meters available in the building section
BuildingSectionAreaFromDate, //the building section can over time have different amounts of available ares
BuildingSectionAreaToDate, //the building section can over time have different amounts of available ares

Tab_RentalContracts:
ContractID,
CustomerID,
BuildingSectionID,
OfficeID,
ContractFromDate, //a rental contract usually has a start date and an end date, alternatively an open end date
ContractFromDate, //a rental contract usually has a start date and an end date, alternatively an open end date
OfficeArea, //how many square meters is the office

Tab_RentalPrices:
ContractID,
OfficeID,
RentalPrice, //annual price pr. sqm
RentalPriceFromDate, //prices can change over time
RentalPriceToDate //prices can change over time

Notice that there are date ranges in all three tables.

My objective is to organize my data so that i can make ONE selection in ONE date field (for example the date of today) and then my qlikview has to give me the follwing information:

- how many sqm are rented
- how many sqm are avalable
- what is the average price pr. sqm. pr. building section today
- how many sqm have an end date (id i have recieved and registered termination)
- forecast for rental earnings next year (the above multiplied with average price)

What is the best way to organize all the data, so that the date ranges are 'on line' and i only need to make one selection?

Month-for-month would be OK.

I was thinking about:
1: creating a table with all existing months in the period from 2005-2020
2: using intervalmatch/join to make three extended tables, where every line exists once pr. month

But i think there must be a better solution?

Thank you in advance.

[#2]

If you have no further needs than those specified above I would just make a graph. Start with making a list of dates (or use an input-value) and call it Date.

Dimension = 'Total' ,

expession =sum (avg or whatever suits your needs) (if(FromDate<=Date and (Date <=ToDate or len(ToDate)<1), "Squaremeters").

[#3]

Thanks, but the problem comes when i need to calculate BOTH the sum of rented squaremeters AND the sum of avalable sqm AND the price in the same step, as the DateFrom is in three different field in three different tables.

[#4 · Accepted solution]

I thought I just give a push in the right direction and you would fill you the blanks yourself. Anyway here is a bit more detaied explanation. I assume the double "ContractFromDate" is a typo.

- how many sqm are rented

sum(if(ContractFromDate<=Date and (ContractToDate>=Date or len(ContractToDate)<1),OfficeArea)


- how many sqm are avalable
what is available? square meters not currently rented, total BuildingSectionArea?


if its sq meters not currently rented, use something like:
sum(if(BuildingSectionAreaFromDate<=Date and (BuildingSectionAreaToDate>=Date or len(BuildingSectionAreaToDate)<1),BuildingSectionArea)
- sum(if(ContractFromDate<=Date and (ContractToDate>=Date or len(ContractToDate)<1),OfficeA)


- what is the average price pr. sqm. pr. building section today

use "building section" and "OfficeID" as dimensions //just to be on the safe side. make it a pivot table and collapse if needed

//if OfficeArea is not consisten for one OfficeID you need to put in constrains using ContractFromDate and ContractToDate

sum(if(RentalPriceFromDate<=Today(1) and (RentalPriceToDate>=Today(1) or len(RentalPriceToDate)<1),RentalPrice*OfficeArea)
/ sum(if(RentalPriceFromDate<=Today(1) and (RentalPriceToDate>=Today(1) or len(RentalPriceToDate)<1),OfficeArea)

- how many sqm have an end date (id i have recieved and registered termination)

sum(if(ContractFromDate<=Date and ContractToDate>=Date),OfficeArea)


- forecast for rental earnings next year (the above multiplied with average price)

sum(if(RentalPriceFromDate<=Today(1) and (RentalPriceToDate>=Today(1) or len(RentalPriceToDate)<1),RentalPrice*OfficeArea)
/ sum(if(RentalPriceFromDate<=Today(1) and (RentalPriceToDate>=Today(1) or len(RentalPriceToDate)<1),OfficeArea)

*

sum(if(ContractFromDate<=Date and ContractToDate>=Date),OfficeArea)

You could use the ContractToDate to calculate the next year earnings of already existing contracts.



Note that quite a few of the expressions above uses sum() so I would suggest that Date-field uses "always one selected value" or that you use a variable.

Hope this helps.

Regards
//Nils

[Miguel_Angel_Baeyens]

Hello Jesper,

You could concatenate both area tables into one, with a new field called say "Type" where you set the source of any of the records "Office" or "Building". Renaming from and to fields when concatenating will leave you with one table where you now can use one from and to date and use them in set analysis. If price for one rental may vary over time, then do not concatenate the Price table, and use it maybe with some intervalmatch or join. If not, then concatenate it too, having one from and one to date fields in one table.

I elaborated a bit more earlier today but, unfortunately, the reply didn't get the forum.

Hope this helps.

[#6]

Thanks for input guys.

Nils:
I was considering the multiple-if()-statements myself, but concluded it would be less than optimal.
I have to be able to calculate not only status pr. month bu also annual average aso.

Miguel:
Concatenation was also on my mind.

Actually i have ended up doin some intervalmatch joins using the different date fields, and it seems to work. Only downside is that each records now exists in my database X times, X being the period of time is was valid (months)

If, for example, a RentalObject was rented for 6 months, then the record would be there 6 times, with 6 different values in the field 'Date'. Same structure with the price records aso. In my formulas i divide with count(distinct Date) to make annual average aso.

[#7]

It's often more flexable and easier to do thing in the script rather than graphs in front end but more tedious. So I'm totally on board why you went in another direction.

An alternativ would be to make keys and use the for the intervalmatch instead. From what I can tell, you don't end up with duplicates and you get easier expressions in front end that way.

Regards

//Nils