4 Replies Latest reply on Jan 17, 2018 1:28 AM by Michal Mokwinski

    Patient Days Calculation

    Jon Rector

      I cannot seem to find a suitable answer to the attached question.


      My data set has multiple date fields (admission date and discharge date being the primary).  What I would like to do is to calculate for each day of the month what our daily census is and show historical values as well.  I know I can display how many admissions and discharges for each day but that doesn't really answer the question.


      So as an example if I had


      Person A

      • Admit Date: 1/4/2018
      • Discharge Date: 1/9/2018

      Person B

      • Admit Date: 1/7/2018
      • Discharge Date: 1/9/2018


      I would want the graph to show

      • 1/4/2018: 1
      • 1/5/2018: 1
      • 1/6/2018: 1
      • 1/7/2018: 2
      • 1/8/2018: 2
      • 1/9/2018: 2
      • 1/10/2018: 0


        • 1. Re: Patient Days Calculation
          Shinichiro Murakami

          Hi Jon


          Assuming you have this data.



          Edit Data source and Pivot data








          • 2. Re: Patient Days Calculation
            Jon Rector

            I think that based on the small data set example that I gave you this would achieve what I asked, thank you for your response.  This does make sense in how to solve.  My data set is significantly larger and it is coming from multiple tables of a database which all combined, there are about 60 columns of data.  With this connection, I cannot pivot the data and I think if I could it would present many other challenges.


            Is there a solution that I could use without Pivoting the data?




            • 3. Re: Patient Days Calculation
              Shinichiro Murakami

              That's the data set problem and you need to use ETL tool to prepare appropriate data set.




              • 4. Re: Patient Days Calculation
                Michal Mokwinski

                Hi Jon,


                your case looks exactly like the challenge we faced in our my company. We wanted to calculate the number of contractors working each day and, as in your case, we had only start/end date.


                This is issue is easily resolved with scaffolding - don't let it intimidate you! You just join your data with a second table with each day as a new record for the date range you need. This way every patient will (one row) will be multiplied over many rows. Using filters you can filter out the dates that are not needed (before start date and after end date).


                I think I used this article initially, but there are a lot of great resources on scaffolding.



                2 of 2 people found this helpful