Forum Discussion
Calculation of average time between two levels
Hi Team,
Thanks for the help in the past.You guys are just awesome.
Could you please help me in this new issue.
I have my dataset as shown below :
| Customer Type | Logged Date | Changed Date | Order Number | Level | Sub Order No |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:16 | 2000 | M1 | 111 |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:29 | 2000 | M1 | 222 |
| L2 | 2019-06-17T06:03:17Z | 23/07/2019 13:32 | 3000 | M1 | 444 |
| L2 | 2019-06-17T06:03:17Z | 31/07/2019 23:02 | 3000 | M1 | 888 |
| L3 | 2019-06-03T05:32:01Z | 05/06/2019 10:22 | 2000 | M1 | 333 |
| L3 | 2019-06-17T06:03:17Z | 21/06/2019 18:45 | 3000 | M1 | 555 |
| L2 | 2019-06-17T06:03:17Z | 16/08/2019 15:35 | 3000 | M1 | 666 |
| L3 | 2019-06-03T05:32:01Z | 25/06/2019 16:41 | 2000 | M10 | 333 |
| L3 | 2019-06-17T06:03:17Z | 22/07/2019 14:00 | 3000 | M10 | 555 |
| L2 | 2019-06-03T05:32:01Z | 12/08/2019 12:13 | 2000 | M10 | 222 |
| L2 | 2019-06-17T06:03:17Z | 20/08/2019 8:51 | 3000 | M10 | 666 |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:41 | 2000 | M4 | 111 |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:41 | 2000 | M4 | 222 |
| L2 | 2019-06-17T06:03:17Z | 24/07/2019 14:51 | 3000 | M4 | 999 |
| L2 | 2019-06-17T06:03:17Z | 01/08/2019 1:29 | 3000 | M4 | 888 |
| L3 | 2019-06-03T05:32:01Z | 05/06/2019 15:29 | 2000 | M4 | 333 |
| L3 | 2019-06-17T06:03:17Z | 24/06/2019 15:41 | 3000 | M4 | 555 |
| L2 | 2019-06-17T06:03:17Z | 16/08/2019 15:59 | 3000 | M4 | 666 |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:28 | 2000 | M2 | 111 |
| L2 | 2019-06-03T05:32:01Z | 08/08/2019 20:29 | 2000 | M2 | 222 |
| L2 | 2019-06-17T06:03:17Z | 24/07/2019 14:26 | 3000 | M2 | 999 |
| L2 | 2019-06-17T06:03:17Z | 31/07/2019 23:02 | 3000 | M2 | 888 |
| L3 | 2019-06-03T05:32:01Z | 05/06/2019 10:22 | 2000 | M2 | 333 |
| L3 | 2019-06-17T06:03:17Z | 21/06/2019 18:45 | 3000 | M2 | 555 |
| L2 | 2019-06-17T06:03:17Z | 16/08/2019 15:35 | 3000 | M2 | 666 |
| L2 | 2019-06-03T05:32:01Z | 12/08/2019 9:30 | 2000 | M6 | 111 |
| L2 | 2019-06-17T06:03:17Z | 24/07/2019 14:27 | 3000 | M6 | 444 |
| L2 | 2019-06-17T06:03:17Z | 25/07/2019 9:00 | 3000 | M6 | 999 |
| L2 | 2019-06-17T06:03:17Z | 02/08/2019 14:30 | 3000 | M6 | 888 |
Scenario is this is an orders table. Each order(e.g 2000) includes multiple sub-orders(e.g 111,222 etc) as mentioned above.Similarly Order 3000 has placed multiple sub orders.
Each of these Sub orders travel through various levels (M1,M6,M2 etc) and users update these sub orders. The raised and changed date are updated as they move from one level to another.
Now I need to calculate
Point 1:
average time spent for all orders (keeping track of Sub orders too) in each of the level e.g
1)Average time spent by all orders from Level 1(M1) to Level 3 (M3) or
2)Average time spent from Level 3(M3) to Level4(M4) or time average calculation between any two levels.
Point 2:
Average time spent by orders in each of the levels(M1, M2,LM etc) i.e If I select M1 in Slicer,it should give me an average time for all the orders spent in M1 and respectively M2,M3 etc
I used the Earlier function for the second point and I guess it worked.
Any other fool proof solution would be appreciated.
Thanks in advance.
Hi Anonymous you are going to have to provide some expected output result examples. For example:
- "Average time spent by all orders from Level 1(M1) to Level 3 (M3) " - I don't know what that means:
- I don't see any M3 orders
- Do you mean all orders in a group for M1, M2, M3?
- Do you mean all orders in the M1-M2-M3 larger group?
- "Average time spent from Level 3(M3) to Level4(M4) or time average calculation between any two levels"
- Again, no M3 orders.
- Do you mean orders for the same customer or any customer, and same order or any order as it moves from M3 to M4?
- How are you selecting the "any two levels?"
Remember, we don't know your data or business at all, so please be as explicit as possible. Except for missing M3 orders, your data looks really good, we just need clearification (or I do at least) on your expected output. You can do the math in Excel and post screenshots if you want as long as we can clearly follow your examples.
- "Average time spent by all orders from Level 1(M1) to Level 3 (M3) " - I don't know what that means:
3 Replies
- edhansCommunity Champion
Hi Anonymous you are going to have to provide some expected output result examples. For example:
- "Average time spent by all orders from Level 1(M1) to Level 3 (M3) " - I don't know what that means:
- I don't see any M3 orders
- Do you mean all orders in a group for M1, M2, M3?
- Do you mean all orders in the M1-M2-M3 larger group?
- "Average time spent from Level 3(M3) to Level4(M4) or time average calculation between any two levels"
- Again, no M3 orders.
- Do you mean orders for the same customer or any customer, and same order or any order as it moves from M3 to M4?
- How are you selecting the "any two levels?"
Remember, we don't know your data or business at all, so please be as explicit as possible. Except for missing M3 orders, your data looks really good, we just need clearification (or I do at least) on your expected output. You can do the math in Excel and post screenshots if you want as long as we can clearly follow your examples.
- AnonymousNot applicable
Hi edhans ,
Thanks a lot for your suggestions.I get your doubts.
Apologies for a buggy dataset and poor summary.
Let me get back with a better explanation on the problem.
I am marking this as close till then.
- edhansCommunity Champion
Sure Anonymous , open a new thread when you are ready and we are all here to jump in. Have a great day!
- "Average time spent by all orders from Level 1(M1) to Level 3 (M3) " - I don't know what that means: