# Alteryx Designer Discussions

SOLVED

## Calculating time in various stages by aggregating the time in multiple columns

Highlighted
7 - Meteor

Hi Community,

I've searched the community for a solution, but my 'natural language processing' isn't up to scratch.

I'm trying to calculate the number of days leads spend in each phase of our sales funnel. The data is currently in the format below Table 1, however, I'd like it to look like Table 2 below. The calculations are calculated based on today's date (9 Jan 2019).

Challenges

1. Depending on the complexity of the customer journey, there can be up to 100 phases/timestamps.
2. Each journey is different. The number of columns is defined by the lead with the greatest number of phases.
3. A timestamp is created every time a lead is opened/actioned. There can be multiple status/phase change timestamps in the same phase (e.g. See phase 2 and phase 3 below).

Objectives

1. Get the data in the format of Table 2
2. Calculate the total time in each unique phase (i.e. if a lead multiple status/phase change timestamps in the same phase, then calculate the total time in that phase).

Table 1

 Lead ID Product Code Sale_Start UPDATE_ON Phase 1 Date 1 Phase 2 Date 2 Phase 3 Date 3 Phase 4 Date 4 1 12405 01/11/2018 04/01/2019 3:11:29 PM Online Form 02/11/2018 4:34:23 AM Sales Contact 02/11/2018 10:07:22 AM Sales Contact 02/11/2018 12:23:45 PM Financial Clearance 04/01/2019 3:11:29 PM 2 90070 22/12/2018 02/01/2019 4:45:30 AM 3 23451 27/11/2018 14/12/2018 12:44:38 PM Online Form 28/11/2018 4:33:07 AM Financial Clearance 28/11/2018 10:13:04 AM

Table 2

 Lead ID Product Code Sale_Start Phase 1 Days in phase 1 Phase 2 Days in phase 2 Phase 3 Days in phase 3 1 12405 1/11/2018 Online Form 1 Sales Contact 0 Financial Clearance 63 2 90070 22/12/2018 Application Received 18 3 23451 27/11/2018 Online Form 1 Financial Clearance 0

Can anyone help me with this? Happy to answer any other questions or clarify the problem if required.

Chris

Highlighted
16 - Nebula

This doesn't deal with multiple timestamps in the same phase yet, but does calculate the number of days in each phase.

Do you want to test this on a bigger dataset to see if it works ok, then we can look at the rest?

Highlighted
14 - Magnetar

Attached please find my solution. Although there's a tool container to reformat back horizontally, if you've got up to 100 phases, that will be a slow process (need to move manually in the join tool, or add a select tool)

Used a bunch of Multi-row formula tools to help with this. After transposing so you can compare dates to dates, the first Multi-row formula takes care of redundant phases - where something is in the same phase from one capture to the next. Only the last of those will pass through the workflow.

The next Multi-row formula tool accounts for the skipped phases above, and makes all phases number consecutively within each Lead ID.

Finally, the last Multi-row formula tool compares the dates. For all phase 1, comparison is to start date (and today's date will be used if the date field is blank). For all other phases, computed between start of the current phase and stamp of the previous phase.

Highlighted
16 - Nebula

Updated version attached that now aggregates multiple timestamps in the same stage.

Highlighted
7 - Meteor

Thanks so much for the solution - solves my problem in a very elegant way. All three posts would have done the job!

Thanks so much for your help!

Labels