Hi All,
I'm trying to calculate the difference in days if there is consecutive disconn and re conn for the same customer.The final column is Difference in days. Below the output:
Customer | Zn | Dt | Act | Diff |
67049 | AM | 16-Mar-17 | Disconnection | |
67049 | AM | 22-Mar-17 | Reconnection | 6 |
67049 | AM | 15-Sep-17 | Disconnection | |
67049 | AM | 18-Sep-17 | Reconnection | 3 |
67074 | AM | 23-Feb-17 | Disconnection | |
67074 | AM | 25-Feb-17 | Reconnection | 2 |
67078 | AM | 15-Jun-17 | Reconnection | |
67098 | AM | 22-Jun-17 | Disconnection | |
67098 | AM | 05-Jul-17 | Reconnection | 13 |
67018 | AM | 07-Jul-17 | Disconnection | |
67018 | AM | 13-Jul-17 | Reconnection | 6 |
67138 | AM | 26-Aug-17 | Disconnection | |
67328 | AM | 06-Jun-17 | Disconnection | |
67328 | AM | 06-Jun-17 | Reconnection | 0 |
Please help.
Best Regards,
Scheruku
Solved! Go to Solution.
Hi @Scheruku,
Can you give more information on what you're trying to achieve as it looks like you've achieved it. I'm not sure what you're asking.
Thanks!
I would suggest the following:
First, parse the Dt column into Alteryx form (yyyy-MM-dd)
Then sort the data by Customer and then Date
Finally, use a MultiRow formula to compute date diff. Expression of:
IIF([Act]="Reconnection", DateTimeDiff([Dt_Parsed],[Row-1:Dt_Parsed],"days"), NULL())
Sample attached
This is some great stuff... Thank you very much for your time.
User | Count |
---|---|
18 | |
14 | |
13 | |
9 | |
8 |