2 Replies Latest reply: Feb 25, 2013 2:56 AM by Hans Lindström RSS

    How to present active members over time

      Hi,

      I have been struggling with a problem for some time now.

       

      I have data from an organization with a lot of members.

       

      MemberInfo, Payouts to Members, Cases, CaseActivities, Contacts between member and
      Organization and so on. To handle these different kinds of activities I have created a Linktable by concatenating the different tables and by using
      different flags for different activities.

       

      So far everything is working fine.

       

      Two of the activities I have stored into the LinkTable is “New Member” and “Member leaving”

       

      Example

       

      LinkTable:

      LOAD MemberID,
           
      Start_Date as Date,
            'New member'
      as ActivityStatus,
            1
      as Flag_NewMember
      Resident Members;

      Concatenate

      LOAD MemberID,
           
      EndDate as Date,
            'Member leaving'
      as ActivityStatus,
            1
      as Flag_MemberLeaving
      Resident Members;

       

      It is now possible to calculate the number of ACTIVE members for any selected time period
      by using a set-expression such as:

       

      Sum({<Year=, Month=, Flag_NewMember={1}, Date={“<=$(=max(Date))”}>}MemberCounter) -

      Sum({<Year=, Month=, Flag_MemberLeaving={1}, Date={“<=$(=max(Date))”}>}MemberCounter)

       

      Here is my problem. In the application I would like to present the number of ACTIVE
      customers over time. For example a Graph showing the number of Active members
      Year by Year.

       

      I want the graph to be flexible, so that I can select a specific Year or a specific
      MemberType and so on, not a static Graph.  

       

      Could anyone help me figure out how this can be made.

       

      Best
      Regards

       

      //Hans 

        • Re: How to present active members over time
          Alessandro Saccone

          Leaving:

           

           

          LOADInline

          [nome1, fine, status

          A, 01/01/2014, L

          B, 30/07/2013, L

          ]
          ;



          Concatenate



          NEW:

          LOADInline

          [nome1, fine, status

          C, 01/05/2013, E

          D, 01/09/2013, E

          E, 05/10/2013, E

          ]
          ;



          periodo:

          LOAD * Inline

          [

          mese, anno, startM, endM

          201301,2013,01/01/2013,31/01/2013

          201302,2013,01/02/2013,28/02/2013

          201303,2013,01/03/2013,31/03/2013

          201304,2013,01/04/2013,30/04/2013

          201305,2013,01/05/2013,31/05/2013

          201306,2013,01/06/2013,30/06/2013

          201307,2013,01/07/2013,31/07/2013

          201308,2013,01/08/2013,31/08/2013

          201309,2013,01/09/2013,30/09/2013

          201310,2013,01/10/2013,31/10/2013

          201311,2013,01/11/2013,30/11/2013

          201312,2013,01/12/2013,31/12/2013

          201401,2014,01/01/2014,31/01/2014

          201402,2014,01/02/2014,28/02/2014

          ]
          ;



          This is the Expression:

          sum(if(status='E' and fine <= endM, 1, 0))

          +
          sum(if(status='L' and fine <= endM, -1, 0))



          IT WORKS

            • Re: How to present active members over time

              Thanks Alexandros. But I dont want to create a Interval Match table since it will make the application slower. I have a lot of members, and each member have different start and end-periods.

               

              If I am using a SET-expression like this, and present it in a graph with dimension Year:

               

              =

              sum({$<[MemberID] = P({1<_MonthSerial = {"<=$(#=max(_MonthSerial))"}>})>}MemberCounter)

               

              Then, each year that is presented have the same value for $(#=max(_MonthSerial)). I want the SET-expression to be calculated for each staple in the graph.

               

              Is this possible?