Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here

Count records based on specified dates

6 - Meteoroid



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 :-). 



- 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". 



IF "Sign_Up_Date" <= "Membership_Start_Date" 


"Sign_Up_Date" <= "Membership_End_Date" 

OR IsNull ("Membership_End_Date") 

Then "Member" else "Previous Member"



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_IDStart_dateEnd_dateSign_up_DateOutcomeWhat I HOPED for
110/12/201710/10/20189/1/2018Member / TRUE5 (all members at Sign_up_Date)


10/1/2018Member / TRUE5
311/01/2018null11/15/2018Member /TRUE4
41/1/201812/31/20181/1/2019Previous Member / FALSE2
52/2/201811/16/20184/1/2019Previous Member / FALSE2






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



17 - Castor
17 - Castor

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 



6 - Meteoroid

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


6 - Meteoroid

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!