How can i create Date Recency's? Ex. 0-6 Months, 7-12 months etc..

[jdelaguila]

Good morning everyone!

Still fairly new with Alteryx and I have a project i can't quite figure out. 

I have a file with dates that range from today going back to 1995.  

I need to create RECENCY field based off the most recent date received by my client. So if today is 6/4/2020 i need to make sure that "0-6 Months" is from 6/4/2020 - going back to 12/4/2020 - and so forth going all the way back.

Below is an example of the field RECENY that i'm trying to create based off of the field DATE.

** Recency is based off of today's date 6/4/2020.  This date will change from month to month **
DATE	RECENCY
5/6/2020	0-6 Months
2/21/2020	0-6 Months
2/14/2020	0-6 Months
2/11/2020	0-6 Months
2/10/2020	0-6 Months
12/17/2019	0-6 Months
11/26/2019	7-12 Months
10/1/2019	7-12 Months
9/5/2019	7-12 Months
4/2/2019	13-18 Months
11/11/2018	19-24 Months

I'm thinking i would need a tool to create the below, and than maybe i can link this back to my data to update the RECENCY field?

Recency_Desc	Start_Date	End_Date
0-6 Months	12/4/2019	6/4/2020
7-12 Months	6/4/2019	12/3/2019
13-18 Months	12/4/2018	6/3/2019
19-24 Months	6/4/2018	12/3/2018
25-30 Months	12/4/2017	6/3/2018
31-36 Months	6/4/2017	12/3/2017
37-42 Months	12/4/2016	6/3/2017
43-48 Months	6/4/2016	12/3/2016
49-54 Months	12/4/2015	6/3/2016
55-60 Months	6/4/2015	12/3/2015
61-66 Months	12/4/2014	6/3/2015
67-72 Months	6/4/2014	12/3/2014
73-78 Months	12/4/2013	6/3/2014
79-84 Months	6/4/2013	12/3/2013
85+ Months	6/4/1920	6/3/2013

Any ideas on what the best solution for this would be? What tools you would recommend i use?  Any thoughts would be greatly appreciated. Thanks as always!  Javier

[rupali_bhise]

Hey @jdelaguila ,

Will really appreciate if you can provide sample data. 

Thanks 

[BrandonB]

This workflow dynamically generates the mapping table based on the dates that you are feeding into it. Take a look and let me know what you think!

 

[rupali_bhise]

Hey @jdelaguila ,

Attaching workflow and screenshot for your reference, I guesses summarize tool works for you with Min & Max function on date column.

Good Luck

[jdelaguila]

BrandonB  - thank you this what i was looking for! Still trying to figure out how it works, but it is giving me the results i need.

I've been trying to integrate some sort of Interface tool that lets me input in a CurrentDate as my CurrentDate could change at anytime - for example instead of 6/4/2020 (today) i may need to enter in 6/2/2020 or 5/27/2020. 

I'm still watching videos on Macros and Interface tools so still haven't created one successfully.  If you have any quick fixes for that part i would be greatly appreciative. Thank you for all your help, i owe you!

Javier 

[BrandonB · Accepted solution]

The date that is used as a reference point is right here:

 

Right now it always pulls in the current date as the point of reference, but another date could be put in here and used in the same way

[jdelaguila]

Brandon thanks again for all the information. If i can bug you for one last question to your workflow?

The End Range and Begin Range have 1 day where they overlap. 

If you see below 0-6 months and 7-12 months has 1 day where they overlap - 12/4/2019.

For 7-12 months and 13-18 months it overlaps with  - 6/4/2019.

I've been killing myself trying to figure out how to do the math - any thoughts?

So ideally 7-12 months begin range would start with 6/3/2019 and so on going down.

end range	begin range	month range text
6/4/2020	12/4/2019	0-6 months
12/4/2019	6/4/2019	7-12 months
6/4/2019	12/4/2018	13-18 months
12/4/2018	6/4/2018	19-24 months

Once again thanks a bunch for all the help.  

Javier

[BrandonB]

You can edit the filter logic here to determine which one you want. It sounds like you want the second part of the formula to say [DATE]<[end range] instead of [DATE]<=[end range] 

 

[jdelaguila]

Brandon - i owe you a drink! Maybe if i see you at the next Alteryx Conference! 

Javier

[BrandonB]

Happy to help and I can’t wait for our next conference! Still enjoying all of the virtual work with our customers, but seeing you all in person and hearing everyone’s stories about what they have built is so rewarding.

I’ll be there!