Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?