12 Replies Latest reply on Feb 15, 2020 10:09 PM by Jillian Milne

    Complex calculated field

    Jillian Milne

      Hi everyone


      i I have a dataset on hospital patients that relates to their ward location during their stay.


      i Want to calculate, if they were transferred before midnight on any given day during their stay, how many hours and minutes they were in the ward they are leaving.


      Factors to consider:


      A patient can be in the admitting ward for more than one day before they are transferred.  My date time stamp is admission time.


      1. I want the calculation to return the hours and minutes the patient was in the admitting ward take on THE DAY OF THE TRANSFER.


      For Example: Patient A was admitted on 1 Jan 2020.  They were transferred to a different ward on 3 January at 10 a.m. so the datediff should return 10 hrs 0 mins as I am only interested in the day the transfer took place.


      Please let me know if this is a clear explanation or whether you need more information.


      The field names I am working with are as follows:


      DT1 = Date time admitted

      DT2 = Date time transferred


      Thanks . I really appreciate all you smart coders out there.

        • 1. Re: Complex calculated field
          Jillian Milne

          I Have had a further relevant thought.


          1. if the patient was admitted on the same day as the transfer to another ward, then the start of the time diff should be the admit time

          2. If the patient was admitted on an earlier day I.e. different day to the transfer, then the start of the time diff should be midnight as it means they were in that ward from midnight.

          • 2. Re: Complex calculated field
            Rohit Yeruva

            Hey Jillian,


            A little difficult to give you a straight answer without knowing your data and it's granularity. However, based on your explanation, assuming that each patient would have a row with admit date and transfer date, I made the calculations you need using mock data. Please find the workbook attached.


            Let me know if this will suffice.



            • 3. Re: Complex calculated field
              Jillian Milne

              Thanks.  Yes the data is one row per patient.  I am actually using tableau prep builder (same code) so I can’t open your tableau workbook until I go to work on Monday and have access to Tableau Desktop. 

              Alternatively could you type the code as I am pretty sure it will work as your assumptions are spot on.  

              • 4. Re: Complex calculated field
                Rohit Yeruva

                Sure!! Here are you calculated fields:


                Duration in Seconds:

                IF DATETRUNC('day', [Sample Admit Date]) = DATETRUNC('day', [Sample Transfer Date])
                THEN DATEDIFF('second',[Sample Admit Date],[Sample Transfer Date])
                ELSE DATEDIFF('second', DATETRUNC('day', [Sample Transfer Date]), [Sample Transfer Date])


                Formatted time: this is to display the duration properly

                IIF([Duration in Seconds] % 60 = 60,0,[Duration in Seconds] % 60) + // Seconds
                IIF(INT([Duration in Seconds]/60) %60 = 60, 0, INT([Duration in Seconds]/60) %60) * 100 + // Minutes
                IIF(INT([Duration in Seconds]/3600) % 24 = 0, 0, INT([Duration in Seconds]/3600) % 24) * 10000 // Hours


                Edit the number format for the above field as below:



                • 5. Re: Complex calculated field
                  Jillian Milne

                  Sorry to be so much trouble but the screenshot cuts off the far right bit of code. Could you please repost?


                  To continue this thread, and now I probably getting in over my head, some patients transfer more than once between wards. For these I need to calculate time diff for each.


                  the way the data is set up is as we discussed one row per patient admission.  Each row can and will most likely be different in values and number of ward changes. There are 72000 rows to start with and I have been tacking this by systematically excluding patients who did not have a transfer of interest.  There are many reasons why so I have created calculated fields for each exclusion so I can report numbers throughout the process. For example started with 72000. Step 1 - excluded patients who were admitted to a ward and discharged with no location changes.

                  Then I did several more. After all the exclusions were applied I ended up with 11000 admissions where a transfer occurred.


                  The maximum location changes for one admission was 24. This is no doubt a data quality error from the source data but for now I am not dealing with such errors as they will be easy to exclude at the end.


                  i have split the 11k cohort into groups depending on how many location changes they had during one admission.  The question I started with was to deal with admissions that only had one transfer which your code addresses (Correct?). Trouble is other patients have more than one location change and I need to calculate the time for each.


                  Tthe way the data is set up is:


                  Admit date time

                  Admit to ward a

                  Transfer 1 from Ward a

                  Transfer 1 To Ward b


                  Transfer 2 date time

                  Transfer 2 from ward b

                  Transfer 2 to ward c


                  Transfer 3 date time

                  Transfer 3 from ward c

                  Transfer 3 to ward d

                  Discharge from ward d


                  so on and so on for each subsequent transfer ( up to a possible 24 sets of values between admission to discharge). The last column on any row is Discharge and Discharge datetime.  The discharge action could appear in the row anywhere depending on the individual patient journey.


                  The problem arises as the source data contained Ward and Bed ( I split the cell and got rid of the bed) so if a patent transferred beds in the same ward, it will be considered a transfer but it is not of interest to me.  I only want to evaluate transfers across the rows when the Ward changes.


                  For example


                  Admit date time

                  Admit to ward a

                  Transfer 1 from Ward a

                  Transfer 1 To Ward a (bed change ONLY)


                  Transfer 2 date time

                  Transfer 2 from ward a

                  Transfer 2 to ward b


                  Transfer 3 date time

                  Transfer 3 from ward b

                  Transfer 3 to ward c
                  Discharge from ward c



                  So if you compare this to the first example above which had 3 transfers of interest, this one only had 2 as the first was a bed change in same ward.


                  As I said no 2 rows are the same.

                  one row might have multiple transfers but if they were bed changes, not ward changes so they don’t count as a transfer.

                  The transfers of interest could appear anywhere along the columns 1-24.


                  I hope this makes sense. It’s difficult to articulate. The trouble stems back from the source data. This exercise will inform the technical person how to write the SQL so the data comes to me in a better format.


                  once I get the hrs mins, that’s when I get to work crunching numbers and finding the story but I am stuck with the task of multiple steps reshaping the data to determine the exact requirements- the discovery phase  

                  • 6. Re: Complex calculated field
                    Jillian Milne

                    I've attached a sample in excel of how the data looks.  I have colour coded the transfers that count so you can see they are all over the place and how when the ward doesn't change, it doesn't count.

                    The datetime fields aren't there as they are further to the left of the spreadsheet but they are called DT1, DT2, DT3 etc for each action there is one.

                    • 7. Re: Complex calculated field
                      Jillian Milne

                      Sorry to keep adding to this story...........


                      The purpose of this work, which I probably should have outlined as context is everything, is I need to transfer the number of hours a patient was looked after in a ward on any given day to the ward they went to as the way salaries are allocated to a cost centre are based where the patients was at midnight.


                      So if a patient is in one ward for 10 hours on a particular day and transfers to another ward before midnight, I will adjust the work hours of the first ward by deducting 10 hours and move those hours to the second ward.


                      So this is why the hh:mm difference is calculated:


                           - from midnight if admitted and transferred to a new ward on different days

                           - from time of admission if admitted on same day as transferred


                      Equally for subsequent transfers the hh:mm difference is calculated:


                           - from midnight if leaving from one ward to arriving at a new ward on different days

                           - from time leaving from one ward to arriving at new ward if occurs on same day

                      • 8. Re: Complex calculated field
                        Rohit Yeruva

                        Wow, that is a lot of context! I will read through and get back.



                        • 9. Re: Complex calculated field
                          Rohit Yeruva

                          Hey Jillian,


                          I looked at your spreadsheet and that is not an ideal option to capture and analyze your data. If this exercise is to give instructions to the technical person, then I've made a few suggestions in the spreadsheet. Please find it attached.


                          Suggestion 1: We are trying to pivot all the transfers such that each record represents a transfer. If you can get the data in this format, you can simply use the above calculations that I shared and you should easily get your numbers. However, even here we have redundant data (From & To).


                          Suggestion 2: Again, pivoting your data to the most granular level possible. Here, the transfer rows only have information about which ward they were transferred to because from the prior record, we can easily retrieve the from ward and time. However, if you want to make your calculations to be simple, you can ask your technical person to retrieve the data using Suggestion 1.




                          • 10. Re: Complex calculated field
                            Jillian Milne

                            Thanks so much for taking the time to read, understand and reply.  I like suggestion 2 but will provide both your suggestions to my tech friend when I complete this preliminary project and document my requirements.


                            I just want to check that the code you provided would work for suggestion 2?  Will the code calculate the difference in hh:mm between the previous dt stamp and use midnight if the dates are different otherwise just use the dt stamps values as they are?


                            So for patient 1 in suggestion 2 it would look at the date and if same calculate datediff  t11,t12 OR if date different 12:00 to t12?


                            Ultimately I need a summary with the following information so I can make the adjustments in the cost centres for each of the ward.  In the example below I would deduct 12:23 from 2B and add it to 1K so you can see why I need the information in this format.


                            PATIENT          FROM:TO          TRANSFER DATE          TIME IN FIRST WARD ON DAY OF TRANSFER


                            Patient 1         1M to 1K              01/01/2019                    12:23

                            Patient 2         4E to 3M              03/02/2019                      7:56

                                                   3M to 4E              05/02/2019                      2:23

                            Patient 3         2M to 3M             03/03/2019                      1:10 

                                                   3M TO 2M            05/03/2019                      5.54


                            Feel free to stop helping me.  You have already done so much.  I appreciate it.



                            • 11. Re: Complex calculated field
                              Rohit Yeruva

                              Hey Jillian,


                              To answer your question straight, No - the code I provided will not work for suggestion 2 (S2). It will work for suggestion 1 though. To made S2 work, we have to create additional calculated fields (might be complex) which will help us bring the previous rows admit date to the current row and then compute our calculations. That's why I said if you want simple calculations go with S1.



                              • 12. Re: Complex calculated field
                                Jillian Milne

                                Thanks for everything. I am confident I am on the right track.


                                I have completed the calculations in a less smart way but it means I am now aware of all the issues with the data as I have reviewed it at its most granular level and I now have a validation result set for when the tech person reproduces it using your suggestions.  Win-win all around even though it took me a very long time to get to the end result. Always the case during the first discovery phase of a new project.


                                Thanks again.