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

    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
          MManders

          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 Wink.

           


          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;