6 Replies Latest reply: Jul 4, 2012 11:27 AM by Marcel Canclini RSS

    count per month between dates

      I have contracts with a startDate and endDate.

       

      Id  |  startDate    | endDate

      1   | 01.01.2011  | 31.12.2011

      2   | 01.05.2011  | 31.12.2011

      3   | 01.01.2011  | 31.12.2012

      4   | 01.05.2011  | 31.12.2012

       

      Now I need to count the active contracts per month.

       

      Result Diagram:

      01/2011 : 2

      02/2011 : 2

      03/2011 : 2

      04/2011 : 2

      05/2011 : 4

      06/2011 : 5

       

      How can I achieve that ?

       

      Many thanks,

      Marcel

        • Re: count per month between dates
          Stefan Wühl

          Are your startDate always at month's start and endDate always on month's end?

           

          You could create a Table with Month start dates, then use an INTERVALMATCH to link your Month date table to the ID periods. Please check INTERVALMATCH LOAD prefix in the Help file.

           

          Regards,

          Stefan

            • Re: count per month between dates

              sadly the start and end dates can be any date.

                • Re: count per month between dates
                  Stefan Wühl

                  So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?

                   

                  My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

                    • Re: count per month between dates

                      swuehl wrote:

                       

                      So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?

                       

                      My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

                      yes, a contract counts to July if it ends somewhere in the middle.

                       

                      I did look at the INTERVALMATCH and it looks promising. But I actually have 2 date ranges (start/end of contract and start/end of month) and the INTERVALMATCH only checks if a single value is within a range.

                       

                      Or maybe I don't get it... :-(

                        • Re: count per month between dates
                          Stefan Wühl

                          I still believe an Intervalmatch should work, but maybe there is an easier solution for you.

                           

                          Try something like

                           

                          TEST:

                          LOAD *,

                          addmonths(monthstart(startDate),iterno()-1) as ContractMonth

                          while monthstart(endDate) >= addmonths(monthstart(startDate),iterno()-1);

                          LOAD * INLINE [

                          Id  ,  startDate    , endDate

                          1   , 01.01.2011  , 31.12.2011

                          2   , 01.05.2011  , 31.12.2011

                          3   , 01.01.2011  , 31.12.2012

                          4   , 01.05.2011  , 31.12.2012

                          5   , 20.02.2011 , 1.12.2011

                          ];

                           

                          And then create a table with ContractMonth as dimension (or =MonthName(ContractMonth), if you like), and then use

                           

                          =count(Id)

                           

                          as expression.