Count records based on specified dates

[Alteregoryx]

Hi! 

For the past week, I have had quite the struggle trying to get this accurate and in truth, I still have a long way to go, hence this hopefull post :-). 

PROBLEM AND 'TERMS': 

- My dataset has 2000 unique 'member_ID'.

- All of these have a column for "Membership_Start_Date" and one for "Membership_End_Date".

- Some of the "End_Dates" are obviously null, since they are still members.

- The data traces back to 10-12-2017 and is updated in SQL every 20th hour (thus, I need the ability to capture it ongoingly too) 

I would like to know how many members we have had on each specific date since the 10-12-2017 and in all relevant future. 

My own shortcommings:

1) I tried "Generate rows" in order to add a column (called "Sign_up_Date") to my data set that listed all the dates between first datapoint and today. 

2) Then I applied a logic to specify when a line should be counted as a member based on "Sign_Up_Date". 

Logic: 

IF "Sign_Up_Date" <= "Membership_Start_Date" 

AND

"Sign_Up_Date" <= "Membership_End_Date" 

OR IsNull ("Membership_End_Date") 

Then "Member" else "Previous Member"

Endif 

However, this doesn't summarize by each day, rather it finds whether or not that specific line is true or false... AS seen here:

Membership_ID	Start_date	End_date	Sign_up_Date	Outcome	What I HOPED for
1	10/12/2017	10/10/2018	9/1/2018	Member / TRUE	5 (all members at Sign_up_Date)
2	10/25/2017	null	10/1/2018	Member / TRUE	5
3	11/01/2018	null	11/15/2018	Member /TRUE	4
4	1/1/2018	12/31/2018	1/1/2019	Previous Member / FALSE	2
5	2/2/2018	11/16/2018	4/1/2019	Previous Member / FALSE	2

THANK YOU ALL IN ADVANCE! :) 

[wdavis · Accepted solution]

Hi @Alteregoryx 

I have attached a workflow which should hopefully help with your issue.

The first formula tool is making sure every Member has an 'End_date' by using the current date to populate as an end date if it is empty (they are still a member).

The generate rows tool is then creating a row for every date that they have been a member

Then the summarize tool is grouping by each date and performing a count on the Member IDs

I hope this helps, let me know if you have any questions

Will

[danilang · Accepted solution]

Hi @Alteregoryx 

I go about it somewhat differently than @wdavis but end up with the same results

This workflow starts by finding how many members were added on each day, how many were subtracted and generating a list of unique days since the first.  After joining and cleanup, the Multi-Row tool adds the current day's delta to the previous day's total.  The bottom Browse just picks out the dates that you listed in your sample output

 

The numbers don't match your's, because your sample has one member joining In Nov 2018 so that person isn't a member in Sept 2018 

Dan

[Alteregoryx]

Thank you, @wdavis - I can't believe how easily and quickly you cracked my puzzle. Still, I am sooo grateful! :) 

[Alteregoryx]

Thank you, @danilang - ended up using the other logic, but I tested yours out too, and you cracked it in a matter of minutes.. You guys and this community rocks!