1 2 Previous Next 24 Replies Latest reply on Mar 2, 2015 5:23 AM by Łukasz Majewski Go to original post
• 15. Re: calculating time intervals with time stamps in the same column

Lukasz,

Thanks! That is exactly  what I was looking for in the first place. The ability to do that without duplicating DB as I saw as an advice in another topic - "just aggregates and simple arithmetics". Perfect! Thanks a lot! Cheers!!!

• 16. Re: calculating time intervals with time stamps in the same column

Lukasz,

I'm having some troubles calculating averages with these "durations".

I understand that we use an aggregation in our formula and that's why tBL does not allow the measure option. My main goal would be to calculate these durations across/for each Ticket/surgery (each group of time stamps for each patient).

Please see my 2 images attached.

Thanks a lot!

• 17. Re: calculating time intervals with time stamps in the same column

What average do you want to calculate? Can you visualize your goal and provide twbx?

• 18. Re: calculating time intervals with time stamps in the same column

Lukasz,

I want to estimate an average time for each duration I calculated.

For instance, in sheet 1, the average duration of surgery in December 4th would be 90,5 .

In sheet 2, the average duration of ORL surgeries in 2014 ,  for instance.

Do you know how I could perform this?

P.S. In the twbxattached (ED dashboard - ED Throughput sheet) , they perform averages because they have each time stamp for each column, and they do not need an IF function for the DATEDIFF function.

Thanks a lot!!

Cheers

Pedro

• 19. Re: calculating time intervals with time stamps in the same column

Can you upload the bigger workbook again as I am nnot able to open it:

As to average durations per month  - see the attached

You may choose another datepart as well (years/quarters/weeks etc)

Is this what you are after?

• 20. Re: calculating time intervals with time stamps in the same column

Lukasz,

There it goes again.

The solution you proposed isn't quite what I'm after.

I'm trying to understand the bottlenecks in our patient flow through the OR.

My general question would be:

(In a specific month/by a specific department/in a specific OR site -- all filters) what were the average durations in each step of the patient flow ?

But something like the table in the twbx attached. I want to see average durations without having the need to have one row per patient (we have several Ks# surgeries per month)

Thanks and sorry for the hassle...

Cheers!!

• 21. Re: calculating time intervals with time stamps in the same column

We also defined some default time intervals. The big pro, as you put it, is the flexibility to incorporate different time intervals in the same viz and perform quick calcs easily. The main con was that, almost each day, we found different time intervals we wanted to look at, and that meant reformulating data all over again, ie., we have 20 time durations, and we want to have the flexibility to see all diferent combinations of time intervals between these 20 time stamps.

That's why we changed it to a single column time stamps. But then, I couldn't find any other way of doing it than using duplicate datasources for each time interval. That makes simple calcs very tedious and graphs like the ones you presented - that are our main point at first - difficult (impossible?) to build...

I think you get the needed flexibility with the new  Level of Detail Expressions  in Tableau 9.0.

What is your data source? If it is a professional database, a subquery column in a custom SQL should relatively easily give you the same as the new LoD.

• 22. Re: calculating time intervals with time stamps in the same column

OK, the big workbook is based on completely different data. It would be easier if you provide some real data sample with those "steps of patient flows" defined. Attached is just the same workbook with one avg value per partition.

1 of 1 people found this helpful
• 23. Re: calculating time intervals with time stamps in the same column

Lukasz,

Thanks! I think this is how far as I can go with the way I have data organized. I wasn't that familiar w/ using window function in this scenario. Thanks!

I'll seriously consider changing my data into each column per time, to allow simpler calcs.

Thanks!

PEdro

• 24. Re: calculating time intervals with time stamps in the same column

Lukasz,

Hi once again.

Sorry for keep coming back to this viz.

This idea you gave in the attached viz worked fine for a specific tipe of report we have where we want to see daily flow on a per patient basis (not the average measure that we discussed later). And once again many thanks for it!!

I've had some difficulty calculating one measure however: it's the turn over time (the difference between one patient leaving the room and the next patient entering the room, in the same room).

So it would be difference between time: "OR - Patient leaves Room" and the time for next patient in that same room: "OR - Patient enters room"

Many thanks!

• 25. Re: calculating time intervals with time stamps in the same column

It requires a table calculation (see attached)

Also, let me recommend a great resource for you where you will be able to find answers to most of your questions