Hello - Need help with multi-row and running total - URGENT HELP NEEDED

[aravindtvis]

I have a set of unique Call ID along with account number and date time when the call happened. What I am trying to do is the following

1. Populate Column D (Call Back within 24 hrs) - if the same account number has a call in the next row that is within 24hrs of the previous call, populate 1 else 0

2.Calculate the total calls backs that happened for each call. For example in row 7 in the file attached, the call ID had 31 call backs after that particular call. (Arrived 31 by summing up all the 1's) 

For example in row 37 (Call ID 38) there is no call within 24hrs and hence it is 0 but in row 38 (Call ID 39) there is a call within 24hrs and hence 1 and Column E is calculated as per the above logic.

How can I populate column D and E in the file attached using Alteryx. Please help.

Thank you,

Aravind

[ShankerV]

Hi @aravindtvis 

Please find the expected output for Q1.

I need some more explanation to work on Q2, as I didnt get the question completely.

Many thanks

Shanker V

[aravindtvis]

Hi Shanker V. Thank you so much for answering Q1.

So what I would need for Q2 is to populate the total number of call back each Call_ID had. For example, if you see the data, for Call ID 6 there are 36 calls after Call_ID 6 that have value of 1. So I need to populate that Call_ID 6 had 30 call backs. Similarly CALL_ID 7 had 29 call backs and so on. But please note that we need to group it by Account Number (Column B)

Was this explanation helpful?

[ShankerV · Accepted solution]

Hi @aravindtvis 

Please find the expected output for both Q1 and Q2.

Also made sure the account number check is taken care too.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV]

Hi @aravindtvis 

To explain how the logic is implemented to work accurately is as below.

Step 1: Input

Step 2: Datetime tool

It helps to convert the date to alteryx date format for calculation.

Many thanks

Shanker V

[ShankerV]

Hi @aravindtvis 

Step 3: To check the call back within 24 hours

Check is made for both account number should be same 

Also check for 24 hours, 24*60 minutes = 1440 minutes

IF [Account Number]=[Row-1:Account Number] AND

datetimediff([Alteryx dateformat],[Row-1:Alteryx dateformat],"minutes")<=1440
THEN 1
ELSE 0
ENDIF

Step 4: Steps starts for Q2 

Many thanks

Shanker V

[ShankerV]

Hi @aravindtvis 

Step 5: Sorting in reverse to check the total count calculation

Step 6: Made sure the account number should be same.

IF [Account Number]=[Row+1:Account Number] AND [Call back within 24hrs]=1
THEN [Row-1:Expected output]+1
ELSE 0
ENDIF

Many thanks

Shanker V

[ShankerV]

Hi @aravindtvis 

Step 7 and 8: Sort it back 

Select only the necessary columns for output.

Expected result after run:

Please let me know if you have further more questions.

Many thanks

Shanker V

[aravindtvis]

Works perfectly. Thank you so much Shanker V!