topic How to get date interval overlapping in QlikView

How to get date interval overlapping

Wed, 02 Mar 2011 17:30:31 GMT

I have a table like this:



prova:
LOAD * INLINE [
 mac_, from_, to_, value_
 m1, 1/12/2005, 31/1/2006, 1000
 m1, 1/12/2006, 31/1/2007, 2000
 m2, 1/12/2005, 31/1/2006, 1500
];

I would like to know the fraction of each interval (from_->to_) in each year in order to calculate the total value_ amount for each year. In the example for mac_ = m1 and year = 2006: 1000*1/12 + 2000 * 1/12.

I think I can find the solution by using several nested if, but is there any ready-made function in qv which returns the overlapping of two time intervals? (i.e.: 1/1/2006-31/12/2006 vs 1/12/2005-31/1/2006)

Thanks in advance for your help

How to get date interval overlapping

fernandotoledo — Wed, 02 Mar 2011 18:22:31 GMT

I didn´t get what you are trying to do exactly, but search QVs help for "Date and Time Function".

You may find a way to solve it in the script using INTERVALMATCH

Regards

Fernando

How to get date interval overlapping

Wed, 02 Mar 2011 18:43:55 GMT

thanks, I haven't found anything useful in date and time functions. The intervalmatch lets me link a single date with an interval while i need to find the intersection of two intervals

How to get date interval overlapping

rwunderlich — Wed, 02 Mar 2011 20:12:06 GMT

I have a feeling I'm misunderstanding the question, but how about:

prova:

LOAD *,

Delta2006 / 365 as Pct2006

;

LOAD *,

RangeMax(RangeMin(to_, MakeDate(2006,12,31)) - RangeMax(from_, MakeDate(2006)) , 0) As Delta2006

;

LOAD * INLINE [

mac_, from_, to_, value_

m1, 1/12/2005, 31/1/2006, 1000

m1, 1/12/2006, 31/1/2007, 2000

m2, 1/12/2005, 31/1/2006, 1500

];

How to get date interval overlapping

rwunderlich — Wed, 02 Mar 2011 20:15:10 GMT

I have a feeling I'm misunderstanding the question, but how about:

prova:

LOAD *,

Delta2006 / 365 as Pct2006

;

LOAD *,

RangeMax(RangeMin(to_, MakeDate(2006,12,31)) - RangeMax(from_, MakeDate(2006)) , 0) As Delta2006

;

LOAD * INLINE [

mac_, from_, to_, value_

m1, 1/12/2005, 31/1/2006, 1000

m1, 1/12/2006, 31/1/2007, 2000

m2, 1/12/2005, 31/1/2006, 1500

];

How to get date interval overlapping

Wed, 02 Mar 2011 20:17:24 GMT

date(max(total <mac_> from_))-date(min(total <mac_> to_))

what you're looking for?