trouble with IntervalMatch, count per month,quarter or year between dates

[b_garside]

My Interval Match generates about 1.2 rows from a 5599 record set.

Once I view it in table viewer it wont render the data in Preview mode. This indicates there was an issue.

What could cause this? Also I not too worried about the Synthetic table but not sure This is doen correctly?

I'm trying to make this work with a master calendar and the main opportunity table where the Created and Close date are stored or originate from.  Ultimately I'm trying to count overlapping periods on a per day granularity, then go to months and Quarters. The intervals vary depending how long the opportunity stays open. I would like to count the overlapping periods which Interval Match is known for doing.

[Opportunities]://Not all fields shown
LOAD recno() as RecID,
Address1,
Address2,
Address3,
AllocMethod,
BillingCurrencyCode,
City,
ClientID,
Floor(If(IsNull(CloseDate),73050,CloseDate)) as CloseDate, //removes timestamp and fill in Nulls as 12/31/2099
     ContactID,
Country,
County,
Floor(CreateDate) as CreateDate,

Floor(CreateDate) as Date, //input to generate min/max dates in Master Calendar

Dual('Q' & Ceil(Month(CreateDate)/3) & '-' & Right(Year(CreateDate),2) as CreateDateQuarterYear,
CreateUser, 

FROM
$(vPathDirectory)Opportunity.qvd (qvd);

IntervalMatch:

IntervalMatch(Date)
LOAD CreateDate,
CloseDate
Resident Opportunities;

Left Join(IntervalMatch)

LOAD RecID,
CreateDate,
CloseDate
Resident Opportunities;

[b_garside]

Ok, the recno() as RecID,  is a unique ID I assign I can use this since its linked into my IntevalMatch?  vs Opportunity.

The table viewer shows this with a one day increment. Which should be able to aggregate at Month and Quarterly views.  It generated about 1.2m records, not too bad. I have 5998 records total in my main Opportunity table

[swuehl · Accepted solution]

Yes, if you can take RecID as an key for an Opportunity, I would just give it a try with e.g. Year-Month as dimension and

=count(Distinct RecID)

as expression.

As said, it's not easy to see if all is o.k. without knowing your complete setup / script.

[b_garside]

Ok, I understand without the full script its difficult to fully verify. But what has posted above is essentially it. I just pulled out the endless list of non relevant fields like and other dimensional data.  Critically as you point out the Interval Match is generating the needed data to count distinct occurrences from the expanded table linked via the recID.

To answer what order I generate the calendar it was before Interval, that much I knew they had to come first.

I used Rob's QVC library to generate it.

Again Stephen you have been a huge help! 

Just needed some reassurance I was on the right path and was understating how to use it.

[swuehl]

You're welcome.

Not knowing the complete script sometimes really gives some headache, for example, I missed the last DROP Fields Start, End; in your previous script snippet, so I wondered why these fields don't show up in IntervalMatch table...

[b_garside]

Yeah my bad, last minute change to get rid of synthetic keys and reduce memory usage.

I will get better at posting as a result. Thanks for not giving up on me.