Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
bwisealiahmad
Not applicable

Trying to handle membership type changes

KF.png

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.

1 Solution

Accepted Solutions
petter
Not applicable

Re: Trying to handle membership type changes

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:

2017-01-05 23_01_45-Qlik Sense Desktop.png

The expression is:

          Sum(Aggr(-Min(Start)=Min(TOTAL <Member#> Start)),Year,Member#))

In plain english:

  1. 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
  2. Min(TOTAL <Member#> Start) will find the very first start date for the member regardless of the year
  3. Comparing the result of 1 and 2 will return a true or false value which equates to -1 or 0
  4. The comparison is to check if we found the very first start date for a particular Member#.
  5. Since we get a -1 we have to invert the sign by putting av minus in front of the comparison
  6. 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.

1 Reply
petter
Not applicable

Re: Trying to handle membership type changes

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:

2017-01-05 23_01_45-Qlik Sense Desktop.png

The expression is:

          Sum(Aggr(-Min(Start)=Min(TOTAL <Member#> Start)),Year,Member#))

In plain english:

  1. 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
  2. Min(TOTAL <Member#> Start) will find the very first start date for the member regardless of the year
  3. Comparing the result of 1 and 2 will return a true or false value which equates to -1 or 0
  4. The comparison is to check if we found the very first start date for a particular Member#.
  5. Since we get a -1 we have to invert the sign by putting av minus in front of the comparison
  6. 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.