Associate master calendar date range to multiple records with different From Date and To Date

[kerkying]

Hi experts,

In QS we know how to create a master calendar off a single date column using the Min and Max ranges and auto populating a range of continuous dates in between.

However, I have a business case where the records have a From Date and To Date, to denote if the record is active or inactive. I have a requirement for front-end users to filter by a similar From Date and To Date, but the range selected needs to sum up the corresponding records in the table. 

Example below:

Account	FROM_DATE	TO_DATE	Amount
A001	24/04/2020	28/04/2020	50
A001	29/04/2020	31/12/4172	100

The expected result when the filtered date range is 23/04/2020 to 01/05/2020 is 150

Is there any way to acheive this via a master calendar solution, instead of having a IF condition to check the date ranges?

[JustinDallas · Accepted solution]

When I have to solve this problem, I generally create a Calendar-Join table with a date, and a Fact table joined to the MasterCal table with a JoinTable. 

Of course, this may not work with your data model depending on how it's setup.  The crux of the idea is that we generate a Date for each day between the FROM-TO dates inclusive.  Then, no matter how the user works the calendar, they will only get the accounts that are active between those dates.

Here is a demo with the data you've given.

DummyData:
LOAD *,
Account AS '%account_key',
DATE#(FROM_DATE_TEXT, 'DD/MM/YYYY') AS FROM_DATE,
DATE#(TO_DATE_TEXT, 'DD/MM/YYYY') AS TO_DATE
;
LOAD * Inline
[
'Account', 'FROM_DATE_TEXT', 'TO_DATE_TEXT','Amount'
'A001',	'24/04/2020', '28/04/2020',	'50'
'A001',	'29/04/2020', '31/12/2020',	'100'
]
;

DROP FIELDS FROM_DATE_TEXT, TO_DATE_TEXT
;

DummyCalJoin:
Load %account_key,
    DayStart(Floor(Num(FROM_DATE + (IterNo() - 1)))) AS '%fact_calendar_key'
RESIDENT DummyData
While FROM_DATE + (IterNo() - 1) <= TO_DATE
;

MasterCalendar:
LOAD 
 DayStart(Floor(Num(TempDate))) AS %fact_calendar_key, 
 Date(DayStart(TempDate)) AS CalDate, 
 Dual(Year(TempDate), Year(TempDate)) AS Year,
;

//=== Generate a temp table of dates === 
LOAD 
 DATE(mindate + IterNo()) AS TempDate,
 mindate,
 maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
 MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');

EXIT Script
;

[JustinDallas]

You also might be able to try something like this using Set Analysis, but your selections as affected by the Calendar Join might make this an inferior solution:

SUM({
<FROM_DATE={">=$(=Min(CalDate))"},TO_DATE={">=$(=Min(CalDate))"},TO_DATE={"<=$(=Max(CalDate))"}>+
<FROM_DATE={"<=$(=Min(CalDate))"},TO_DATE={">=$(=Min(CalDate))"},TO_DATE={">=$(=Max(CalDate))"}>+
<FROM_DATE={"<=$(=Min(CalDate))"},TO_DATE={">=$(=Max(CalDate))"}>+
<FROM_DATE={">=$(=Min(CalDate))"},TO_DATE={"<=$(=Max(CalDate))"}>} Amount)

That's off the top of my head, so it most likely won't parse, but it's a start.  Notice the '+' sign which is the OR operator in Set Analysis.

[kerkying]

Hi Justin,

Thanks for your reply and the sample script you have attached. This works well for the requirement, the only tweak I did as I was facing a "TO DATE" with an excessively large date (eg. 31/12/4172) was to limit the iteration of dates generated.

While FROM_DATE + (IterNo() - 1) <= TO_DATE

 changed to:

While FROM_DATE + (IterNo() - 1) <= IF(TO_DATE>Date(ReloadTime(),'DD/MM/YYYY'), Date(ReloadTime(),'DD/MM/YYYY'), TO_DATE)

Overall, this was a great help - thanks!