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.
TEST - Membership.qvf 176.0 K