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

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

dadumas — Sat, 16 Nov 2024 00:29:26 GMT

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

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

vamsee — Fri, 12 Jun 2020 15:01:38 GMT

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)

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

dadumas — Fri, 12 Jun 2020 17:49:58 GMT

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