community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Count records based on specified dates

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

null

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

 

THANK YOU ALL IN ADVANCE! :) 

 

 

Highlighted
Alteryx
Alteryx

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

Aurora

Hi @Alteregoryx 

 

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

 

WF.png

 

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

 

Sample.png 

 

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

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

 

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!

Labels