Hi All,
I have the following data:
ID | Name | Country Name | Today's Date |
7623 | Kane | A | 8-Feb |
8936 | Parma | A | 8-Feb |
7729 | Francis | Y | 8-Feb |
5591 | Jenny | M | 8-Feb |
1933 | Alice | C | 8-Feb |
4729 | Beth | B | 8-Feb |
I have added the last column, 'Today's date'. But that also needs to be created on Alteryx.
After creating that column, what I need to do is give an ageing category to the records: >30 days and <30 days
This data has to be run every month and then after reconciliation, some records will be left and they would appear again in the next month's data.
For example, if ID 7729 in the above data appears again in the next month's data, it should have an ageing category against it.
How can we do this in Alteryx?
You can use DateTimeDiff([Field1]),DateTimeNow() ,"days") to calculate the number of days between a date in your data and today. With the number of days, you could use a formula tool or the tile tool to bucket the days. It's not clear why you would add today's date to your data, though. Can you explain if you are adding today's date for this calculation or adding the date so that the date the data is added is saved in the data?
Hi!
You can use DateTimeNow() to record in your field "Today´s Date" and create another field with a formula: 'Date Range - days'
if DateTimeDiff(DateTimeNow(),[Today´s Date],'Days') > 30 then '>30 days' ELSE '<30 days' ENDIF
So you are going to have a field to control this range.
Here's a sample way to achieve what you want:
My 2 cents would be to make sure you track your snapshot dates for your raw data for when you do your ageing analysis. Otherwise things get messy. If you have an internal system generation date, that would be a more consistent date than what you are asking for.
technically it should
if DateTimeDiff(DateTimeNow(),[Today´s Date],'Days') > 30 then '>30 days' ELSE '<=30 days' ENDIF