Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Grouping Data with Arbitrary Dates

evanguyen
5 - Atom

Hello Alteryx Community! :)

 

I am running into an issue with grouping Active Customers together by arbitrary dates. For example, customers that have Start Dates >= Arbitrary Date1 and customers that have Arbitrary Date1 <= End Dates will be grouped together. We would like to know how many Active Customers for January 1st 2017, January 2nd 2017, January 3rd 2017 and so forth. So, customers that have Start Dates >= January 1st 2017 and customers that have January 1st 2017 <= End Dates will be considered all Active Customers for January 1st 2017.

 

I was able to create those Arbitrary Dates in Alteryx with the Generate Rows tool. However, I run into the issue of joining these Arbitrary Dates with my Customer data for comparison.

 

Can someone please help me? Or, have any other suggestions for how to accomplish this task? Workflow and Customers list attached.

 

Thank you so much!

 

2017-12-14_7-12-50.png

5 REPLIES 5
NicoleJohnson
ACE Emeritus
ACE Emeritus

Try something like the attached... if you use the Generate Rows tool to also create a record for every date between each customer's start & end date, you can then use the Join tool to match each [Date] field from your two lists to find out how many customers were active on any given date. Hope that helps!

 

Cheers,

NJ

evanguyen
5 - Atom

Nicole, you are incredible! Thank you so much! :)

evanguyen
5 - Atom

Hi Nicole - Do you have any advice on how to go about grouping the data if we wanted to know a count in between two dates? So, how many members have Start Date > Arbitrary Date1 and End Date <= Arbitrary Date2.

NicoleJohnson
ACE Emeritus
ACE Emeritus

Sorry for missing this response earlier! Not sure I totally follow what you're asking, but I'll give you a couple scenarios:

 

1. If you want to do a count of customers whose start & end was between two dates (i.e. didn't start before arbitrary date 1 and ended before arbitrary date 2), then you can use the original workflow but add a filter before the summarization tool. Filter for [start_date] < [arbitrary_date1] && [end_date] <= [arbitrary_date2]. You could also swap the statements to determine how many customers were active the whole time (started before, ended after).

 

2. If you want to know the # of individual active customers between the two dates without grouping on each day, you can use two summarize tools in place of the one at the end of the original workflow - the first to group by RecordID & Active Customers (to get a list of RecordID's that were active during the specified time frame), then the second to summarize just Active Customers to get a count of unique customers who were active at some point during the period.

 

See attached for some examples! Is that what you were looking for?

 

NJ

 

evanguyen
5 - Atom

Hi Nicole,

 

It is not quite what I was looking for, but we no longer need that piece completed. I really appreciate your quick response and help though! :)

 

Eva

 

 

Labels