Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Date ranges in multiple fields - how to/best pract...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2010-12-10
10:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Date ranges in multiple fields - how to/best practice

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.

- Tags:
- date_and_time

805 Views

1 Solution

Accepted Solutions

Not applicable

2010-12-11
12:04 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

390 Views

6 Replies

Not applicable

2010-12-10
12:51 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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").

390 Views

Not applicable

2010-12-11
06:09 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

390 Views

Not applicable

2010-12-11
12:04 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- 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

391 Views

Miguel_Angel_Baeyens

Employee

2010-12-11
02:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Not applicable

2010-12-13
01:50 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

390 Views

Not applicable

2010-12-16
05:46 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

390 Views