1 2 Previous Next 24 Replies Latest reply on Mar 2, 2015 5:23 AM by Łukasz Majewski

    calculating time intervals with time stamps in the same column

    Pedro Ramos

      Hi guys,

       

      I'm  trying to perfom a patient flow dashboard... something similar to this Healthcare Analytics | Tableau Software

      I have several "times" (and several "time descriptions") all in the same column ... I want to have different time intervals (e.g. the most obvious is surgery duration = surgery ends - surgery starts , but other could be "transportation time: inpatient_arrivesOR - inpatient_patient call ")

      For similar questions, answers have been duplicate DB. Is there any other way, maybe a simpler one?

       

      Thanks in advance!

        • 2. Re: calculating time intervals with time stamps in the same column
          Pedro Ramos

          Hi guys I've been looking at the data behind the "Throughput dashboard" at the Healthcare analytics (Healthcare Analytics | Tableau Software)

          They organize their data  using each time stamp for each column (Column A - Arrival time; column X - Triage Date/time ; Column Y - Discharge Date/Time) and presumably one row per patient.

           

          In my data, I have them across rows (column A - Date/time and column B - Date/Time description ; and for each row different time stamps for the patient flow across the hospital) , so several rows per patient.

           

          Based on your experience, what is better for duration calculations?... I'm having some problems with my current approach (I'm using duplicate datasource to calculate durations : time stamps between different rows) ...

          Thanks!

          • 3. Re: calculating time intervals with time stamps in the same column
            Łukasz Majewski

             

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

              Seems to me that the dates as their own columns would make things much easier in Tableau, assuming you are looking to track things like average time between X and Y and showing the flow between phases and doing things like the sample dashboard you referenced.

               

              If you have a standardized set of times that you capture, making the dates their own columns may be the best choice.  Of course, if you add more "Time Desc" categories, your data structure will need to change which makes things more complicated.

               

              Given the struggles you are describing with the Row format of the dates, maybe you can mock up your data as columns and see how that works for your particular needs?

               

               

               

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

                Pedro,

                 

                I would like to offer another suggestion.  Having columns as I originally suggested will make things easier and avoid a lot of table calculations.  The downside is that you may have some challenges based on how you actually want to analyze the data.

                 

                While I was having my lunch (my mind wanders... ) I think I came up with a way that will meet more needs.  I have attached an Excel file and a TWBX that has the specifics.  The high level description is this:

                * Create one row for each stage of the timeline for example "Time Between Starting Anesthesia and Starting Surgery"

                * This row will have the timestamps and the duration of that stage

                * Your "Time Desc" field will represent the stage and can be used for sorting (manual sort, not alphabetical)

                 

                The record is no longer a single point in time and is now an event with a defined start and end.  These rows can now be charted and filtered very easily using Tableau basic functionality.

                 

                If you need a more detailed explanation of the data that I created and why, just let me know.

                 

                 

                 

                There will likely be some work to perform ETL of the data to create this type of dataset if the data you posted here is very similar to what you are starting with.  It is not very hard ETL, but it is some.

                 

                I think a situation like yours needs to determine where you want the complexity.  In my mind the complexity should be in reformatting your data, then the analysis and presentaion become trivial and very flexible.

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

                  Mark,

                   

                  First of all, thanks a lot for the time you devoted on thinking about my problem. (another) great thing about TBL is how friendly you people in the community are, specially for guys like me that are just giving the first steps in data viz (yet really enthusiastically!).

                   

                  The idea you proposed was how we originally thought about this data. 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...

                  So, this got me here...

                  I'm just wondering if, before jumping into changing data once again - now for one row per patient/ different columns for each time stamp) like the one in the healthcare analytics example - , there won't be other problems we'll face.

                  Specifically, with data in that format (Column A "Patient in the room" Column B "patient starts anesthesia" Column C "Patient starts surgery " (...) "Column Z "Cleaning room finishes"), will I have problems performing simple calcs/graphs like the one you presented, or would it just be simple DATEDIFF for each interval we wanted, and then perform simple agg measures...?

                  (or, why do you think having data in the "stage format" is better than in the "event by event format" ?)

                   

                  Thanks, ... any thoughts on this is really appreciated!

                  Cheers!

                  • 7. Re: calculating time intervals with time stamps in the same column
                    Pedro Ramos

                    Mark,

                     

                    Look at this twbx and tell me what you think plz...

                     

                    Any potential problems in specific analysis/graphs/vizs we could want? (and that in "stage format" could be easier?)

                    Thanks and cheers !!

                    • 8. Re: calculating time intervals with time stamps in the same column
                      Pedro Ramos

                      Lukasz,

                       

                      Only saw your mockup now.

                      That surely gets me somewhere also! for that specific viz (that actually was one real viz people wanted to build here) it is plain useful!

                      Really nice played...

                      Any thoughts on what we've been discussing?

                      Thanks!!!

                      • 9. Re: calculating time intervals with time stamps in the same column
                        Łukasz Majewski

                        From what you conveyed so far I think the source data layout is suitable from database pov - i.e. { [event], [time_stamp]}. And it is not too difficult to calculate duration - could be done with SQL in source too. I would only suggest introducing kind of event/time type reference table which you could use to conveniently group or filter your data (joined on unique time type ID which you have already).

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

                          Pedro,

                           

                          I think it really comes down to what your goals for the data will be.  Since requirements will always be changing, think in the shorter term and things you know you need now and are likely to be the next few things you are likely to need.  Trying to solve for every, unknown, possible option rarely works well for this kind of stuff.

                           

                          Honestly, I think if you create the one row per surgery, you will be able to go pretty far with it.  Since DATEDIFF is not a table calculation, you will be able to further manipulate the data pretty easily.  As your data needs evolve, you may need to again think about reformatting.

                           

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

                            Lukasz ,

                             

                            Using duplicate DB (twbx attached), do I have to make one sheet for each duration ?

                            I wanted to make a single table that could present a resume of the time duration per patient (or per a group of patients).

                            Having a different sheet (table) for each time duration would make patient flow difficult to follow...

                             

                            Thanks!!

                            • 12. Re: calculating time intervals with time stamps in the same column
                              Łukasz Majewski

                              I don't understand what you are trying to do now. What I meant was one more field would help to group those various events, e.g. surgery (start, end), room (enter, leave), etc. It may then be used to partition the calculation or filter the view.

                              • 13. Re: calculating time intervals with time stamps in the same column
                                Pedro Ramos

                                Lukasz,

                                 

                                My previous twbx is how we are doing the durations calc, considering our current DB formatting (one column for time stamps / several rows per patient for each time).

                                I understand your idea.

                                Could we achieve a single table w/ multiple durations (time intervals)

                                • 14. Re: calculating time intervals with time stamps in the same column
                                  Łukasz Majewski

                                  There are many ways to achieve this, I guess. Attached are 2 sheets with different approaches without use of table calculations - just aggregates and simple arithmetics. But I am not familiar enough with your data and do not know which way to go.

                                   

                                   

                                  1 2 Previous Next