Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
So in the table above I have a member (1495) and he is started a membership 03.05.1996 and it is still active. The issue is that the 25.01.2012 he changed membership type and now has a new beginning date (26.01.2012).
When I want to count historical date (how many new members this year, last year etc.) this creates an issue because now he has two beginning dates and the first one is correct, but it counts both 03.05.1996 and 26.01.2012 as starting values when it should only use 03.05.1996 because this member is still active, but has changed membership type.
Any tips how to handle this? Eventually how I can flag if it is a change or he has ended his membership. If there is no new startdate that means that the end date is the final and he isn't active anymore.
Accepted Solutions
There are several ways to approach this. Let's assume that a person gets a new membership number if she/he doesn't just change membership type but end the membership and at a later date becomes a new member.
You can solve it in the load script - but I show you a solution that seems to work directly in a chart without changes to the data model:
The expression is:
Sum(Aggr(-Min(Start)=Min(TOTAL <Member#> Start)),Year,Member#))
In plain english:
- Min(Start) since it is inside an Aggr will find each Member# for each Year and then get the minimum start date for that person
- Min(TOTAL <Member#> Start) will find the very first start date for the member regardless of the year
- Comparing the result of 1 and 2 will return a true or false value which equates to -1 or 0
- The comparison is to check if we found the very first start date for a particular Member#.
- Since we get a -1 we have to invert the sign by putting av minus in front of the comparison
- And Aggr() will return 0,1 or multiple values of ones or zeros for each year and those has to be summed - which since they are only zeros and ones equates to a count. So the outer Sum() serves that purpose.
Maybe there is a much simpler solution - but this seems to work.
If anyone seeing this see a more straightforward way of doing it please comment.
There are several ways to approach this. Let's assume that a person gets a new membership number if she/he doesn't just change membership type but end the membership and at a later date becomes a new member.
You can solve it in the load script - but I show you a solution that seems to work directly in a chart without changes to the data model:
The expression is:
Sum(Aggr(-Min(Start)=Min(TOTAL <Member#> Start)),Year,Member#))
In plain english:
- Min(Start) since it is inside an Aggr will find each Member# for each Year and then get the minimum start date for that person
- Min(TOTAL <Member#> Start) will find the very first start date for the member regardless of the year
- Comparing the result of 1 and 2 will return a true or false value which equates to -1 or 0
- The comparison is to check if we found the very first start date for a particular Member#.
- Since we get a -1 we have to invert the sign by putting av minus in front of the comparison
- And Aggr() will return 0,1 or multiple values of ones or zeros for each year and those has to be summed - which since they are only zeros and ones equates to a count. So the outer Sum() serves that purpose.
Maybe there is a much simpler solution - but this seems to work.
If anyone seeing this see a more straightforward way of doing it please comment.
