topic Calculated Variable in QlikView

Calculated Variable

Tue, 24 Jun 2014 09:46:17 GMT

Dear All,

I have a problem but my capacity on qlikView are too light to solve it myself and I need the community help.

I have a table Entities (representing Companies), each company have a contract with a begin date and a duration.

For Exemple I have the company MEGATRON with a contract starting in 2012-02-01 and 12 month period.

So for this company we have 3 contracts :

2012-02-01 to 2013-01-31 (Period 1)

2013-02-01 to 2014-01-31 (Period 2)

2014-01-31 to today (Period 3)

If my user select 2012-05-20 I need to compute the data for the period 1, but if my user select 2013-04-06 I need to compute the data for the period 2013-02-01 to 2014-01-31 (Period 2).

Is this possible ?

Thanks a lot for your help.

Re: Calculated Variable

Gysbert_Wassenaar — Tue, 24 Jun 2014 09:51:31 GMT

Certainly. There are several ways. You could use the intervalmatch function. See this blog post: IntervalMatch. But perhaps creating reference dates with a loop is easier. See this blog post for more information: Creating Reference Dates for Intervals

Re: Calculated Variable

Tue, 24 Jun 2014 13:17:43 GMT

Hi Gysbert,

Thanks for your answer, I think "Reference Dates for Interval" is the good answer, but I need a little more help.

My table "Contracts", doesn't contain all contract, just the first one, how I can extrapolate the 2 next ?

In my previous example, the table contracts contains :

ContractID 105

EntityID 3

StartDate 2012-02-01

Periodicity 12

So I don't know how to generate each period to fit the data in the Creating Reference Dates for Intervals.

Thanks again !