Data modelling - Fact with valid from - valid to

BojeErvenius — Thu, 28 Jun 2018 19:59:47 GMT

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.

Re: Data modelling - Fact with valid from - valid to

roelemanskiblue — Mon, 16 Jul 2018 20:08:58 GMT

I think you are looking for this approach:

Creating Reference Dates for Intervals

Re: Data modelling - Fact with valid from - valid to

BalaBhaskar_Qlik — Tue, 17 Jul 2018 09:43:27 GMT

may be:

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

topic Re: Data modelling - Fact with valid from - valid to in QlikView

Data modelling - Fact with valid from - valid to

Re: Data modelling - Fact with valid from - valid to

Re: Data modelling - Fact with valid from - valid to