Alteryx Designer Discussions

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

Automatic identification of the most recent date record for further calculations

7 - Meteor

Dear community, 

 

rigth now I am facing an issue that I am not able to solve by myself. (All data is attached)

 

1. Issue: I would like to calculate the difference between two dates (2019-03-18 and the most recent date). This data input will be updated each month once and imports new dates within a new column (darte of end of most recent month). Now I would like to build a workflow that recognizes the most recent date (2020-08-31) and will then be substracted by the oldest date (2019-03-18). 

2. Issue: I would like to calculate the date between the most recent month and the previous month. In this case (see data output below) this would be 2020-08-31 subtracted by 2020-07-31. Ideally, as described by issue 1, the workflow would recognize the most recent date (column) and the previous month automatically. 

 

-> The results of issue 1 and 2 should be new columns.

 

MaxSchG_0-1601976756829.png

 

 

Thanks in advance, 

Max

 

Alteryx Certified Partner

Hi @MaxSchG,

 

Do you need the workflow to estimate the date diff between the most recent and oldest dates dates just for the first row or for each one of your rows?

Alteryx Partner
Alteryx Partner

Hi @MaxSchG , give this a try if it satisfies your requirement. Also I have removed the unwanted rows let me know if you want whole data will join and resend the workflow.

 

grazitti_sapna_0-1601978624643.png

 

Thanks.

Sapna Gupta
17 - Castor

Hi @MaxSchG ,

 

This what i able to understand. You want subtract (2019-03-18 and the most recent date) and subtract 2020-08-31 subtracted by 2020-07-31 dynamically by finding recent date column, oldest date column, previous month of recent column.

 

Hope this helps and near to your requirement 🙂

 

7 - Meteor

Hi  @grazitti_sapna thank you so much for your quick response. Its perfect that you already removed the unwanted columns. However, I cant open the workflow. Could you resend it in the .yxmd file format so I can open it with my version of Alteryx? I only need the Workflow as a stand alone .yxmd file. 

 

Thank you for your help!

 

Best,

max

17 - Castor
7 - Meteor

Hi @atcodedog05 thank you for your workflow. Is it possible that you remove the irrelevant dates and also transform the differences between the dates in "Months"? I think right now the difference is shown in days. That would be awesome! 

 

Thanks, 

Max

17 - Castor

Hi @MaxSchG 

 

Sure will be done.

17 - Castor

Hi @MaxSchG 

 

Here is the workflow with changes:

 

Output:

atcodedog05_0-1601980369611.png

 

Just deselect the unwanted columns in select tool. You can rename too.

atcodedog05_1-1601980417767.png

 

Hope this helps 🙂

 

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

17 - Castor

Hi @MaxSchG 

 

Made an updation. The date which falls in the criteria will be selected on auto.

Output:

atcodedog05_0-1601981188529.png

 

Hope this helps 🙂

 

 

Labels