Avg number of days using rows of date ranges

[Jazyk]

Hello I am trying to get the average number of days it takes a sales team to submit their report in a 12 month period.  The data needs to be grouped by employee ID.  

For Example Employee 105575 have several submitted dates on this report.  I need to know what the average time lapses before this employee submits his report in a given 12 month period.  

Any suggestions?/Help?

Thanks so much!

[Qiu]

@Jazyk 
I think a DateTimeDiff function should do the work.

Can you provide the data in a text or Excel format?

[Jazyk]

Thank you! I am attaching the file.  I was trying the datetimediff but could not figure out how to get the average. Fairly new to Alteryx.. thanks for your help

[AngelosPachis]

Hi @Jazyk ,

After your formula tool that uses a datetimediff function to find the difference in days, you can use a summarize tool to find the average for each employee (Group By Employee ID, Avg Diff)

[Jazyk]

Hello AngeloPachis,

Thank you for your workflow below.  Your formula contains datetimediff for Last submitted date to paid date, however I am trying to see the average for all last submitted dates for each employee. (Paid date does not pertain to information I am trying to resolve) The last submitted dates are on separate rows and not sure how to write a formula for datetimediff and are on separate rows.  For example if an employee submits a report 1/15/21 then 1/30/21 and then 2/28/21.. the number of days between report 1 to 2 is 15 days, then from report 2 to 3 is 28 days.. they want to how frequently in days the employee submits their reports.  Trying to avoid an employee from submitting their reports every 6 months.  

[AngelosPachis · Accepted solution]

Hey @Jazyk,

You're right, since you want to compare dates in the same column a formula tool won't work. Try using a multi-row formula instead, where for each employee/employee ID you are finding the datetimediff of the current row to the one above. The calc will restart for each employee, and will return nulls where you have a single entry and 0 when you have two entries that have been submitted the same date

Cheers,

Angelos

[Jazyk]

Thank you!  Thank worked.  Appreciate sharing of your Alteryx knowledge.