5 Replies Latest reply on Jan 10, 2019 8:31 AM by Ken Flerlage

# Distinct customers with multiple transactions to determine averages

Tableau 2018.3.2

Issue: Customer visits office for service.  Customer can have multiple transactions during their visit.  There is a row of data for each transaction containing the customer id, wait time and a transaction time for each individual transaction or service performed.   The customer's wait time is the same for all three transactions; while each individual transaction time is different.  I need to calculate the Average Wait Time and Transaction Time for customers per day, week, month etc.  If a customer has three transactions; the same wait time is presented three times and the average is not calculated properly.  I'm sure there is an easy fix - I've tried LOD:  Include and Fixed Customer ID, but that didn't work.

 Wait Time Customer 1 0:51:08 Customer 1 0:51:08 Customer 1 0:51:08 Customer 2 0:47:02 Customer 3 0:36:23 Customer 3 0:36:23 Customer 3 0:36:23 TABLEAU AVERAGE WAIT TIME 0:44:14 REAL AVERAGE WAIT TIME 0:44:51
• ###### 1. Re: Distinct customers with multiple transactions to determine averages

Could you potentially provide a small sample of the data?

• ###### 2. Re: Distinct customers with multiple transactions to determine averages

Here is a sample, representing the customers mentioned in my post.

• ###### 3. Re: Distinct customers with multiple transactions to determine averages

You should be able to use an LOD for this.

Before doing that, Tableau is recognizing these wait times as dates, so I started by converting it to minutes using the following:

Wait Time Minutes

// Convert to in minutes (with decimals)

FLOAT(DATEDIFF('second', #12/30/1899#, [Wait Time]))/60

Then, I created the following LOD which should give us a single value for each customer visit:

Customer Wait Time

// Use a fixed LOD to get the wait time for each customer visit.

{FIXED [Customer ID], [Ticket Number]: MAX([Wait Time Minutes])}

Then, when you average this field overall, you should get the correct value.

See attached.

• ###### 4. Re: Distinct customers with multiple transactions to determine averages

Ken thanks - this seems to work except that I need to represent Average wait time in hh:mm:ss

Also, there are 5000+ customers per day at 75 locations, so I need to report this daily, weekly, monthly, quarterly, etc.. for each location; eight Regions; and of course "all" - will this still work at those levels?

I had originally converted Wait Time to "seconds" using "datediff('second" #00:00:00# [wait time]) but I didn't use the FLOAT or date that your calc contains...

• ###### 5. Re: Distinct customers with multiple transactions to determine averages

This calculation basically ignores the multiple records, giving you a single wait time for each customer/ticket number, so it should work when you average at different levels. Just keep in mind the Tableau order of operations if you create filters. See Tableau's Order of Operations - Tableau. There may be some filters that you wish to apply before calculating the LOD. In that case, be sure to make them context filters.

To format as hh:mm:ss, I'd suggest changing the Wait time calculated field to show seconds:

Wait Time Seconds

// Convert to in seconds

DATEDIFF('second', #12/30/1899#, [Wait Time])

Then create another calculated field to calculate the average:

Avg Wait Time

AVG([Customer Wait Time])

Then one more that will do a bit of magic math I found here: Convert numbers to hours:minutes

Avg Wait Time HH:MM:SS

// Seconds

[Avg Wait Time]%60

+

// Minutes

IIF(INT([Avg Wait Time]/60) %60 == 60, 0, INT([Avg Wait Time]/60) %60) * 100

// Hours

+ INT([Avg Wait Time]/3600) * 10000 //hours

Finally, drag this to your view, then right-click on it and choose format. Change the format to a custom format like this:

In the end, you should have the right number.

Finally, your calculation, datediff('second', #00:00:00#, [Wait Time]) would work as well. It's doing the same thing.

See attached.