Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
dadumas
Creator II
Creator II

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

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

 

Labels (1)
2 Replies
vamsee
Specialist
Specialist

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
Creator II
Creator II
Author

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