3 Replies Latest reply: Nov 29, 2015 11:10 AM by Aravind Sasidharan RSS

    Set analysis and rangesum

    Aravind Sasidharan

      Hi,

       

      I am trying to use set analysis and range sum to compute the total number of records that were active at a some point in the month

       

      For example, if these are the records:

       

          id state created_date modified_date

          1 expired 01/12/2014 10/12/2014

          2 expired 01/12/2014 10/03/2015

          3 active 01/12/2014 01/12/2014

          4 expired 10/01/2015 12/01/2015

          5 expired 10/01/2015 11/03/2015

          6 active 10/02/2015 10/02/2015

          7 expired 10/03/2015 11/03/2015

       

      The expected o/p is

       

       

          Dec-14 3

          Jan-15 4

          Feb-15 4

          Mar-15 5

       

       

      E.g: For Jan-2015 the result should be 4 -

        1 active record from Dec 2014 + 2 created in Jan + 1 from Dec which actually expired in March 2015            

        

      The modified_date is updated when a record is expired

       

      I tried this: 

       

          rangesum(above(Count({$<[state] = {'active'} >} id), 0,12))

          + Count({1<[state] ={'expired'}, modified_date.Calendar.Month ={">=$(created_date.Calendar.Month)"}>} id)

       

      I couldn't figure out how to do the second count statement. How can I aggregate the records which are currently not active but were active at some point during the month.Is it possible to achieve this without using separate calendar varables ?

       

       

      Thanks in advance!   

        • Re: Set analysis and rangesum
          Stefan Wühl

          You can do it like HIC demonstrated in

          Creating Reference Dates for Intervals

           

          Then you just need to add a master calendar:

          The Master Calendar

           

           

          INPUT:
          LOAD *,
            if(state = 'active', today(), modified_date) as end_date
          INLINE [
            id, state, created_date, modified_date
              1, expired, 01/12/2014, 10/12/2014
              2, expired, 01/12/2014, 10/03/2015
              3, active, 01/12/2014 ,01/12/2014
              4, expired, 10/01/2015, 12/01/2015
              5, expired, 10/01/2015, 11/03/2015
              6, active, 10/02/2015, 10/02/2015
              7, expired, 10/03/2015, 11/03/2015
          ];
          
          
          id_x_Dates:
          Load id,
               Date( created_date + IterNo() -1 ) as ReferenceDate
          Resident INPUT
          While IterNo() <= end_date - created_date + 1 ;
          
          
          Load Date,Date as ReferenceDate, Month(Date) as Month, Monthname(Date) as YearMonth ;
          Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ; 
          Load Min(ReferenceDate)-1 as MinDate, Max(ReferenceDate) as MaxDate resident id_x_Dates;
          

           

           

          Then create a straight table with dimension YearMonth and expression

          =Count(Distinct id)

           

          YearMonth Count(Distinct id)
          7
          Dez 20143
          Jan 20154
          Feb 20154
          Mrz 20155
          Apr 20152
          Mai 20152
          Jun 20152
          Jul 20152
          Aug 20152
          Sep 20152
          Okt 20152
          Nov 20152
            • Re: Set analysis and rangesum
              Stefan Wühl

              If you don't need the reference dates on a daily basis (e.g. you don't want to drill down to week or dates), you can even simplify the script to

              INPUT:
              LOAD *,
                if(state = 'active', Date(today()), modified_date) as end_date
              INLINE [
                id, state, created_date, modified_date
                  1, expired, 01/12/2014, 10/12/2014
                  2, expired, 01/12/2014, 10/03/2015
                  3, active, 01/12/2014 ,01/12/2014
                  4, expired, 10/01/2015, 12/01/2015
                  5, expired, 10/01/2015, 11/03/2015
                  6, active, 10/02/2015, 10/02/2015
                  7, expired, 10/03/2015, 11/03/2015
              ];
              
              id_x_Dates:
              Load id,
                  MonthName(AddMonths( created_date, IterNo() -1 ) )as YearMonth
              Resident INPUT
              While MonthStart(created_date, IterNo() -1 ) <= Monthstart(end_date);