I am trying to calculate the average difference between order_time and pickup_time grouped by runner, but in customers table order_time can appear more than once and the calculations go wrong because of this
- customers_order_table
| order_id | customer_id | pizza_id | exclusions | extras | order_time |
|---|---|---|---|---|---|
| 1 | 101 | 1 | NULL | NULL | 2020-01-01 18:05:00 |
| 2 | 101 | 1 | NULL | NULL | 2020-01-01 19:01:00 |
| 3 | 102 | 1 | NULL | NULL | 2020-01-02 23:51:00 |
| 3 | 102 | 2 | NULL | NULL | 2020-01-02 23:51:00 |
-ruunners_orders_table
| order_id | runner_id | pickup_time | distance | duration | cancellation |
|---|---|---|---|---|---|
| 1 | 1 | 2020-01-01 18:15:34 | 20 | 32 | NULL |
| 2 | 1 | 2020-01-01 19:10:54 | 20 | 27 | NULL |
| 3 | 1 | 2020-01-03 00:12:37 | 13,4 | 20 | NULL |
| 4 | 2 | 2020-01-04 13:53:03 | 23,4 | 40 | NULL |
My calculated field is working like (image): (9 10 21 21 15 15)/6
But it should be: (9 10 21 15)/4
It is getting 2 info about the same order
The only solution I found is to create a new table without duplicated values like this:
| order_id | runner_id | pickup_time | order_time |
|---|
Any other suggestion?
CodePudding user response：
It seems like you may want something like {FIXED order_id: max(pickup_time - order_time)} because it sounds like you need a single time delta for each order. Then hopefully Tableau will let you take the average of that calculation when you have runner_id and the new field in the view
I changed my calculated field from
DATEDIFF('minute',[Order Time],[Pickup Time])
to:
{ FIXED [Order Id]: max(DATEDIFF('minute',[Order Time],[Pickup Time]))}
Now the average calculation is correct, no more duplicates enter image description here
