Set Analysis using a Master Calendar over Start and End Dates without loading row by row facts

[dadumas]

This may be a hard one, for all you set analysis gurus

I have a Contract table:

[Contract Number]

[ Contract Start Date]

[Contract End Date]

[Number of Months]                 = End Date - Start Date in months

[Monthly Contract Value]      = the value of the contract each month, goes out into future

I have implemented a Master Calendar with a DateKey, Date, Year, Month, MonthYear, etc........    lots of columns.  I am expert at creating a Master Calendar Design.

I wish to create a measure:  sum([Monthly Contract Value]), that reacts to Date Range selections in my Master Calendar.

I wish to avoid creating an exploded table with each contract period in a separate row, rows going from start to end date - I am doing that now, works fine, but bloats the data model.

So.....

I started going down the road of set analysis with : (Date is a column in the Master Calendar)

Sum({$<[Contract Number]={"Date>=$(=max([Contract Start Date]))<=$(=max([Contract End Date]))"}>}[Monthly Contract Value])

While this is is syntactically fine, since there is no direct association between Contract to MasterCalendar, date selections  on the Master Calendar do nothing.

Is there a way to accomplish this where I can avoid the inclusion of an exploded out table?  

Dave

[vamsee]

Hi,

According to my understanding of your question, this is how I would process this.

Create a table based on https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944

This table would be different from your master calendar

In the expression, I would use the reference date, something like

Sum({<Reference_Date=P(Date)>}Amount)

[dadumas]

That is what I am currently doing and works fine.  However, this extra table adds many millions of rows to my table design.

Example 100,000 contracts X 60 months each = 6 million rows.  These rows serve no purpose other than to connect an "exploded" contract period date to a date dimension.

I am hoping for a better way, such that I do not need the intermediary table with the 6M rows.  

Dave