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;

[Anonymous]

Start by removing synthetic keys. you have several of them

[swuehl]

Why are you creating this field in [Opportunities] table:

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

The Intervalmatch using the Date field as in 

IntervalMatch(Date)
LOAD CreateDate,
CloseDate
Resident Opportunities;

is supposed to create a link to a Date field outside Opportunities table, like in a master calendar, isn't it?

[b_garside]

The Date field was used to link with and provide the dates for the master calendar which I use in some charts where Create date is applicable Dim.

The problem I think is how to leverage the Calendar and tie it into the IntervalMatch to get a proper count of overlapping records based on the create/close dates..

I know this is not modeled correctly just confused how to leverage this function. I know it requires a junction table to join the main fact table to the Interval Table. which Dim field

Again All I'm trying to do is count overlapping Opportunities to see how many are concurrently open at any given period such as a Month or Quarter where I would use the Master calendar.

[b_garside]

Yeah I was trying to link to the master calendar. Not sure how the interval can be used in a Chart in terms of Dim field.

I assume I have to use the field from the Interval Table maybe generate an Interval Match from Calendar?

Do I link it by date or RecID, I'm so lost on this??

Im open to other options. I know there are other articles none seem to fit. I feel my desired outcome is fairly straight forward?

[b_garside]

Synthetic keys removed. Now I just need some help on how to link this to the main Opportunities table.

Then what type of Date Dim and Expression would use to count the overlapping Opportunities?

I have a Unique key called RecID, as well.  I could use  for  count( Distinct RecID )  ?

[b_garside]

Does this looks correct?

[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,
'Q' & Ceil(Month(CreateDate)/3) & '-' & Right(Year(CreateDate),2) as CreateDateQuarterYear,
CreateUser,

     Floor(CreateDate) as Start,
Floor(CloseDate) as End,

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

  
IntervalMatch:
IntervalMatch(Date)
LOAD 

Start,
End
Resident Opportunities;

Left

Join(IntervalMatch)
 

LOAD

RecID,
Start,
End
Resident Opportunities;

[b_garside]

The problem now is determining if I have the correct expression? 

Right now I'm using Count (Distinct RecID)

But not sure if that is correct for counting open records that overlap ? over a span of months and some up to three years.

[b_garside]

I'm basically trying to do what this guy did, but I want to be able to count per month or Year or Quarter.

I was hoping my Calendar would take care of that piece.  Re: count per month between dates

[swuehl]

It's quite hard to see if this is correct (without knowing your complete script and lookin at the results).

Right now, I would say it's not. If you examine the IntervalMatch table per table viewer, what do you see? A Date per RecID for all dates within the resp. period?

At what point in the script are you building your master calendar? It must exist before using the IntervalMatch.

If your required granularity to count Opportunities is not on a day level, but month, quarter, year wise, this approach will create a lot of records in your Intervalmatch that is not needed (but still should work in principle). You can create a master calendar that supports a monthly (and less granular) analysis, maybe by creating two date records per month for the first and last date of a month and use this for your interval match.

In principle, if your intervalmatch worked out, using a master calendar dimension and an expression

=count(Distinct Opportunity)

should return what you need.

Would it be possible that you upload a small sample file with some mock up sample records contained (e.g. in an INLINE table)?