2 Replies Latest reply: Jan 25, 2010 10:14 AM by Martien Kemperman

# Date setanalysis End of month

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.

• ###### Date setanalysis End of month

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 calendarTempCalendar:LOAD DISTINCT monthend(\$(vDateMin) + RowNo() - 1) AS DateNumber, monthend(Date(\$(vDateMin) + RowNo() - 1)) AS TempDateAUTOGENERATE 1WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);`

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

`Temp:INTERVALMATCH (TempDate)LOAD DISTINCT Startdate, EnddateRESIDENT [table with startdate and enddate];JOIN ([table with startdate and enddate])LOAD Startdate, Enddate, TempDateRESIDENT Temp;DROP TABLE Temp;`

• ###### Date setanalysis End of month

Hi Mark,

Thanks for your suggestion. It did work.

Gr

Martien