Data modelling - Fact with valid from - valid to

[BojeErvenius]

Hi,

I have a set of data with customer contracts, about 1 million rows. Every contract have a valid from and a valid to date fields. I would like to create a model in Qlikview with a fact table and dimension tables.

One solution is to create an interval match between the fact table valid from – valid to date and the master calendar date. This works perfect for calculating most of the expressions. For example number of contracts at any point in time.

The problem.

I would like to visualize the number of contracts in different years for different age groups. Or create an expression like “number of contracts by customers of age 25”. Since a person’s age is not same for the duration of the contract period I am not sure how to design the model, or write expressions to handle this.

[roelemanskiblue]

I think you are looking for this approach:

Creating Reference Dates for Intervals

[balabhaskarqlik]

may be:

Count(Aggr( sum{$<age = {'25'}>Customer} ( Distinct  Customer)))