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 **DATERECENCY5/6/20200-6 Months2/21/20200-6 Months2/14/20200-6 Months2/11/20200-6 Months2/10/20200-6 Months12/17/20190-6 Months11/26/20197-12 Months10/1/20197-12 Months9/5/20197-12 Months4/2/201913-18 Months11/11/201819-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_DescStart_DateEnd_Date0-6 Months12/4/20196/4/20207-12 Months6/4/201912/3/201913-18 Months12/4/20186/3/201919-24 Months6/4/201812/3/201825-30 Months12/4/20176/3/201831-36 Months6/4/201712/3/201737-42 Months12/4/20166/3/201743-48 Months6/4/201612/3/201649-54 Months12/4/20156/3/201655-60 Months6/4/201512/3/201561-66 Months12/4/20146/3/201567-72 Months6/4/201412/3/201473-78 Months12/4/20136/3/201479-84 Months6/4/201312/3/201385+ Months6/4/19206/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

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

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

Macros

Date Time

Best Practices

Dynamic Processing

Data Investigation

Database Connection

Iterative Macro

Accepted answers

BrandonB

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

current date.png

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

All comments

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!

months mapping table.png

months mapping table.yxmd

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.

Date Recency.png

Good Luck

Date Recency.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703