Calculating sum of length of date-ranges, without intersections...

[#1]

Dear fellow Qlikviewers,

I would like to build an expression (it will be impossible to generate the data in a table beforehand, too many possible combinations selection-wise) that calculates the length of multiple dateranges, but without counting overlapping dateranges double.

DateFrom	DateTill
2012-01-01	2012-12-31
2012-02-01	2012-07-01
2012-06-01	2012-11-30

Hence, for the above table, I would expect a length of a year.

My intuition to solve the problem:

1. Enumerate per daterange all the possible unique dates within that range
2. Concatenate these date-sets together in a single set
3. Use COUNT DISTINCT to count the unique date occurrences only

The third step seems sensible enough, but the earlier two I can't manage. I figured I could do the first using ValueLoop( 0, [DateTill] - [DateFrom] ) and adding the returned values to [startdate] to get a list of all possible dates.

The trouble is that if I do stuff like ValueLoop(0, [DateTill] - [DateFrom]), I somehow always seem to get '-' in my table, if I use literals it executes the ValueLoop just fine. I figured this was because it doesn't actually execute ValueLoop 3 times (as by the above table), but actually passes an array of daterange-lengths, which is not what it expects... Or at least, I don't see any other option by now! I have played around with AGGR to make sure that the parameters passed are per record, but without the intended effect.

Does anyone have a different intuition about how to solve this, or a better way to implement step 1-2? It would be greatly appreciated, thanks in advance!

[swuehl · Accepted solution]

Have you thought about using a calendar with all dates in the total period you are looking at, then use an INTERVALMATCH LOAD (check the Help for more detail) to link the calendar dates to your periods.

Finally just do a distinct count of calendar dates.

If you could upload a small, self containing QV sample (i.e. one should be able to reload), I think someone might help you with the syntax.

Regards,

Stefan

[#3]

Well I didn't know about that command, and although I had actually considered to create a calender I somehow thought I would also have to exhaustively generate calendars for all the selection combinations, which of course is nonsense (hence my remark in the first sentence about not wanting to generate the data in advance). I feel kind of stupid now, but thanks for making me see it I'll give this a try, and post my results here!

[#4]

That did the trick indeed!

Like you said, I created a table with a composite key with (date-start + date-end), together with a column containing all the dates in between. Linked that up with a table by creating the composite key in the same way, and could use count distinct. Much easier than what I had in mind, and fast also Thanks a lot!