9 Replies Latest reply on Oct 3, 2016 10:34 AM by Vishal Patel

# How do I divide time by a whole number and return time?

Hi,

I am trying to divide a number (which I have converted to seconds) by a whole number and return back to a time format. In Excel, it works fine, but using the same logic in Tableau does not...any ideas?

Note: The column named Cal THT takes the time i have in seconds and converts to hh:mm:ss. I am trying to divide it by the first column Inbound calls (my failed column that doesn't work in Tableau is named AHT)

< Tableau results. The last column is not reflecting what Excel shows below. The Cal THT in Tableau takes # Seconds/ 86400 and formats to HH:MM:SS.

< The AHT column is a calculated column that takes Cal THT/Total IB Calls. Cal THT is not a calculated column; I named it to stay consistent.

Any ideas why I can't get this to work in Tableau?

• ###### 1. Re: How do I divide time by a whole number and return time?

I cannot replicate your excel formula.

Could you attach your excel file?

Shin

• ###### 2. Re: How do I divide time by a whole number and return time?

Hi Shin,

Please see the attached Excel file for the example. The calculation is done within the Pivot Table.

• ###### 3. Re: How do I divide time by a whole number and return time?

Actually seems like you mixed up day calculation and hour, min, sec calculations.

let me confirm your final expectation.

You want average AHT with [hh];mm;ss format, correct?

Thanks,

Shin

• ###### 4. Re: How do I divide time by a whole number and return time?

Yes, that is correct.

What I did for Cal THT in Tableau was convert what was there to all seconds and than use a calculation of <seconds>/86400 and format to hh:mm:ss to get back to hh:mm:ss.

But when I divide Cal THT/Total IB Calls in Tableau, it comes up with something completely different than I would expect. I'm expecting the answer I see in Excel.

• ###### 5. Re: How do I divide time by a whole number and return time?

I tired but cannot make it by very easy way.

I mean it's impossible to show aggregated "date/hour" format and normal number at same time in multiple measure value".

So put three measure separately, follow post for detail procedure.

Such a simple view, it required 100 steps!! - Having Multiple KPIs - - Still Struggling with Excel ?? <Tableau's Room>

Anyway to calc "date" value.

Table and Excel handle date data differently.

Tableau starts from 1899/12/30, while excel starts from 1990/1/1 or even allows something unrealistic 1990/1/0.

To remove day part from hhmmss.

I created the formula

[THT Days]  // this is recognized as "days"

[Total Handle Time]-DATEPARSE("yyyyMMddhhmmss","18991230000000")

and to format as hhmmss

[THT (hhnnss) ]

DATEPARSE("hhmmss","000000")+sum([THT Days])

same thing for AHT

[AHT dyas]

sum([THT Days])/sum([Total IB Calls])

[AHT (hhnnss)]

DATEPARSE("hhmmss","000000")+[AHT (days)]

To put three measures separately, follow post for detail procedure.

Such a simple view, it required 100 steps!! - Having Multiple KPIs - - Still Struggling with Excel ?? <Tableau's Room>

Thanks,

Shin

• ###### 6. Re: How do I divide time by a whole number and return time?

To get THT days this formula is much cleaner and understandable.

[THT days 2]

[Total Handle Time]

-datetrunc('day',[Total Handle Time])

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: How do I divide time by a whole number and return time?

Thank you Shin for your help and insight. I will try this and get back to you with my results.

Greatly appreciated!

• ###### 8. Re: How do I divide time by a whole number and return time?

Sounds good.

Good luck!

Shin

1 of 1 people found this helpful
• ###### 9. Re: How do I divide time by a whole number and return time?

It worked great. Thank you for your help again!