Date setanalysis End of month

[#1]

Hello experts,

I suffer the following challenge( and I got no clue how to solve it !)

In my DB I got records wih a startdate and an enddate.

These records should be counted on every monthend day in between the start- and enddate.

Example:

Startdate=01-03-2010 and Enddate=15-07-2010

This record should be counted on 31-03-2010, 30-04-2010, 31-05-2010 and 30-06-2010.

Count ({$<[Num lastdayofmonth]={'>=$(=([Num startdate])<=S(=([Num enddate])'}>} [recordnumber])

Lastdayofmonth = calculated and used as Dimension in Chart )

Num Startdate = Num(startdate)

Num Enddate = Num(enddate)

I hope anyone has an solution or a tip.

[#2 · Accepted solution]

I think you need to use INTERVALMATCH for this.

First you create a table with every enddate possible for your application and data (borrowed and addapted code .

Calendar:
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(Today()));

// Autogenerate calendar
TempCalendar:
LOAD DISTINCT
 monthend($(vDateMin) + RowNo() - 1) AS DateNumber,
 monthend(Date($(vDateMin) + RowNo() - 1)) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Now you can use INTERVALMATCH to find the ones that fit into your ranges

Temp:
INTERVALMATCH (TempDate)
LOAD DISTINCT
 Startdate,
 Enddate
RESIDENT [table with startdate and enddate];

JOIN ([table with startdate and enddate])
LOAD
 Startdate,
 Enddate,
 TempDate
RESIDENT Temp;

DROP TABLE Temp;

[#3]

Hi Mark,

Thanks for your suggestion. It did work.

Gr

Martien