1 2 Previous Next 15 Replies Latest reply on May 7, 2014 5:34 AM by Shawn Wallwork

# Time as a Measure

I keep a summary of the data my agency looks at based on the month. One of the data points we look at is an average amount of time it took to complete an operation. I want the average operating time to be a measure, but i'm unable to just drag it down to measures. Can someone please shed some light?

• ###### 1. Re: Time as a Measure

What does your dimension look like for that Average Amount of Time? Is it a date? I assume it's a dimension since you're wanting to 'drag it down'. What format is the value expressed in? Of course the easiest thing to do is put just that one field in a packaged workbook and post it (we really don't need any of your other data.

Cheers,

--Shawn

• ###### 2. Re: Time as a Measure

The time is expressed in hours and minutes.

• ###### 3. Re: Time as a Measure

Can you eliminate all the other fields, and upload the workbook with just this dimension, as Shawn suggested?  That'll be the best way for you to get help.

• ###### 4. Re: Time as a Measure

Military time?

• ###### 5. Re: Time as a Measure

It's not showing the time of day. It's how long an operation lasted. For example, in January the average time an operation lasted was 3:12.

• ###### 6. Re: Time as a Measure

Change the data type from a date to a number. Then you can use it as a measure.

--Shawn

• ###### 7. Re: Time as a Measure

I have a similar problem, but changing it to a number isn't a sufficient solution.  It changes my "time spent" from 1:30 (for 90 minutes) to -1.937.  Any other ideas?

My data are in this format (showing both Hr:Min and Decimal format):

 Show TSL (Hr:Min) TSL (dec) show 1 1:30 1.50 show 2 1:35 1.58 show 3 1:40 1.67 show 4 1:45 1.75 show 5 1:50 1.83 show 6 1:55 1.92 show 7 2:00 2.00 show 8 2:05 2.08 show 9 2:10 2.17
• ###### 8. Re: Time as a Measure

Gwynne, on this one you really need to post a sample workbook because Tableau makes different guesses about data it connects to. So until we know exactly what you're starting with it is extremely difficult to know how to help you, especially when it comes to dates. For instance I copy/pasted this:

 Duration 1:20 1:25 1:30

From Excel into Tableau and got this:

Duration

12/30/1899 1:20:00 AM

12/30/1899 1:25:00 AM

12/30/1899 1:30:00 AM

This makes complete sense to me, because I know how Tableau thinks and how Excel thinks about dates. There are ways to manipulate that second table to get the results you want. But to do this we need to know:

1. Where's the data coming from, and in what form?
2. What's the data look like when you bring it into Tableau (sample workbook).
3. What's your ultimate goal? How do you need the data to exist and be manipulated?

Thanks,

--Shawn

• ###### 9. Re: Time as a Measure

Hi Shawn –

I don’t seem to have an option to insert a file (a video or photo I could).  Maybe I’m missing something?  I’ve attached an Excel sheet with the kind of data I’m working with.  It is the amount of time people spent listening to our shows.

I would like to trend this by show over time to be able to say whether the average time people spend listing to a show like Morning Edition is increasing or decreasing.  Internal clients are used to seeing that expressed as hr:min, not in decimal format (though clearly that’s an option, and seemingly a far easier one, but I don’t want to have to have that conversation a hundred times â˜º).

When the data are in Tableau, they look like this – like I said, negative numbers:

Let me know what I’m missing on uploading a file to the discussion, or if this is enough for you to work with, thanks in advance for your help!

Gwynne

• ###### 10. Re: Time as a Measure

Yeah these forums take a bit getting used to. The only thing you 'missed' was that the same place you attached that Excel file (bottom of the Use Advanced editor page) is also the same place you could have attache a packaged workbook (twbx). Don't make the mistake of attaching a twb accidentally because it won't include any data, so we won't be able to open it.

I'll take a look at your data and get back to you.

--Shawn

• ###### 11. Re: Time as a Measure

Based on your data, you can manually calculate the time elapsed:

Insert a custom calculation as:

DATEPART('hour', [Time Spent Listening (Hr:Min)]) + DATEPART('minute', [Time Spent Listening (Hr:Min)])/60

• ###### 12. Re: Time as a Measure

Gwynne, see the attached workbook. You said you wanted to track time spent watching over time, so I had to add an extra date field to your data. The chart shows your 9 shows charted over time (days) based on how long the show was watched. And it is formatted so you don't have to have a 'conversation' with every internal client .

The thing to note is all of this is done by carefully selecting the right type of pill (or field) to use, and then formatting both the fields and the axises correctly. All of these things interact with each other to either produce what you want or break the viz. Then when you break the viz, there is usually some sort of calculation that can be used to 'fix it'. Again this will entail changing some of the formatting and/or data types, and changing the axises formatting and possibly changing the data type.

I guess my point is: the closer you can get to providing a detailed (and accurate) representation of the actual data (NOT the actual data just a representation), then the better we can help you. So take another look at what your data looks like, scramble it up and then connect to it, and post (attach) a packaged workbook in your response, and I'll be happy to help you get what you want. After all I love Morning Edition! So I'm happy to help.

--Shawn

• ###### 13. Re: Time as a Measure

Or you could just format it that way, and eliminate the calc.

--Shawn

• ###### 14. Re: Time as a Measure

Thanks, Shawn.

I figured out how you formatted the date axis - for anyone reading this later who doesn't want to poke around in the fields to find it, here's what you need to do: select the axis, right click, pick format, and under the "Dates" field, use a custom format and type "hh:mm."

My format on my Excel sheet (where the data live) is set to: [h]:mm:ss.

1 2 Previous Next