How to present active members over time

[#1]

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 

[alexandros17]

Leaving:

LOAD *  Inline

[nome1, fine, status

A, 01/01/2014, L

B, 30/07/2013, L

];



Concatenate



NEW:

LOAD *  Inline

[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

[#3]

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?