Calculating Task Time

I am very new to Alteryx and need to calculate time spent on each transaction. I have my data sorted by UserID and then by Time descending. I want to subtract the transaction time of the current task from the time of the previous task to get the task time as long as the UserID is the same. This would go into a new column titled "Task Time." Thank you for the help.

Alteryx Forum - Task Time Question.xlsx

Workflow

Accepted answers

@palberty the tool you're after is called the Multi-Row formula. It lets you calculate values based on previous/future rows.

Here's a link to a lesson about the multi-row: https://community.alteryx.com/t5/Learning-Paths/Advanced-Certification-Learning-Path/ta-p/660137#done

see picture below on where to click to get through to the lesson:

CalculatingTaskTime.yxzp

All comments

One way of doing this.

Many thanks

Shanker V

Calculating Task Time.yxmd

@palberty the tool you're after is called the Multi-Row formula. It lets you calculate values based on previous/future rows.

Here's a link to a lesson about the multi-row: https://community.alteryx.com/t5/Learning-Paths/Advanced-Certification-Learning-Path/ta-p/660137#done

see picture below on where to click to get through to the lesson:

CalculatingTaskTime.yxzp

If needed in minutes, just added a formula tool.

Or we can also replace with the formula

IF [UserID]=[Row+1:UserID]
THEN datetimediff([DateTime_Out],[Row+1:DateTime_Out],"minutes")
ELSE null()
ENDIF

Many thanks

Shanker V

Calculating Task Time.yxmd

@ShankerV don't let the excel preview trick you. There are actually seconds in the underlying data:

Hi @BS_THE_ANALYST

Oh, I missed it as I copied the data and pasted as a text input as the workflow will not be dependent on input file.

Thanks for highlighting...

Many thanks

Shanker V

@ShankerV I didn't know the copy and paste wouldn't pick it up!? Glad you let me know. Thanks!

Hi @BS_THE_ANALYST

Also I have a contradiction, where I am using Alteryx advanced version.

When i imported the data from excel, it auto corrects to the ISO date.

Then I will not use the Datetime tool, and when it is used in other older versions my workflow will provide and incorrect result/error.

That's why I copy and paste the data in text input tool to make sure it work in all versions.

Many thanks

Shanker V

Thank you very much for the help.

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15879

MarqueeCrew 13708

apathetichell 13703