CODEWITHSUNDEEP
tableau-desktop
Luis 2023
Luis 2023

Reputation: 85

Tableau Calculated Field- Duplicated Data

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

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

How_the calculated field is working

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?

Upvotes: 0

Views: 257

Answers (1)

Luis 2023
Luis 2023

Reputation: 85

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

Mako212

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

Upvotes: 0

Related Questions