5 Replies Latest reply on Dec 29, 2018 8:12 AM by Don Wise

    Calculate rate within index column

    Kai Peng

      Hi I have a table for a clinic, and I want to calculate the no show ratio during patient's first, second, third...visit. I have created an index function for patient appointment list and then I stuck. 

      I want to calculate the no show rate among their first, second, third.... tenth visit.


        • 1. Re: Calculate rate within index column
          Don Wise

          Hello Kai,

          Not sure what the expected end result should look like, but the attached 2018.3 workbook should point you in the right direction...number of table calc's in this and at least one nested table calc to get you to what is hopefully the expected ratio against the indexed number of appointments.  Hope it helps! Thx, Don

          Screen Shot 2018-12-26 at 1.28.09 PM.png

          1 of 1 people found this helpful
          • 2. Re: Calculate rate within index column
            Kai Peng

            Hi Don,

            Thanks for the response and the solution. This is really good! And its something I definitely will look into after!

            What I am looking for is I want to collect all the patient's first visit, and then calculate the no show rate. So for example,


            Patient      Date         Status           index      

                1          1/1/08      no show             1

                            2/1/08      chk out               2

                            3/1/08      chk out               3

                2          3/1/09      chk out               1

                            4/1/09      chk out               2

                            5/1/09      chk out               3


            Then for all the first visits the rate is 50%,

            for all the second visits, the rate is 0%

            for the third visit the rate is 0%. so far and so forth.


            Sorry for the misunderstanding, however your solution is definitely on my agenda! So thanks very much!




            • 3. Re: Calculate rate within index column
              Don Wise

              Hi Kai,

              I spent a little bit of time on this and it will be a challenge for anyone to complete.  Ideally, and we all know that this may not be possible due to limitations with the production of the data, but ideally, each appointment date would have a transaction number by each patient ID as part of the original data set.


              Reason being, using either PREVIOUS_VALUE() or INDEX() functions, limits what we can do for an LOD calculation to further summarize. We can't use either of those functions inherently in an LOD to help summarize the data.  Additionally, if there were a transaction ID number for each patient (sequentially like the INDEX() function), then we could use that as a DIMENSION and more easily get the summarized results you're looking for.  As it stands, nothing I did in the attached produced a Dimension that I could work with to summarize.


              However, I did get you part way there in the attached 2018.3 workbook.  At least it'll point you in the right direction.  I stopped at a certain point with some of the calculations (i.e., stopped at appointment #20, however there are some patients that have as high as 94 appointments) and you or someone would have to continue what I did thereafter for to address each instance of appointment type.  Hopefully it helps.  Thx, Don


              Initially we know that overall Visit Status types, the 'No Shows' account for 8.87% of all appointment dates:


              Screen Shot 2018-12-27 at 11.30.21 AM.png

              Here I built out a table to capture and flag which 'Visit #" also was a 'No Show', with a 0 representing False and 1 representing True.  We cannot total on discrete measures, this is where things stalled because I couldn't get anywhere with creating a Dimension (not a Measure).

              Screen Shot 2018-12-27 at 11.31.17 AM.png

              Duplicating the table and then representing the % of each visit against the Total # of Appointments:

              Screen Shot 2018-12-27 at 11.31.23 AM.png

              Then exported the % table to Excel to get the totals by Visit #.  Again, someone would have to build out a calculation to capture each and every instance type of visit and associate a flag to it as per the attached. If you were to build that out, then the totals below should add up to 8.87% overall.

              Screen Shot 2018-12-27 at 11.27.58 AM.png

              1 of 1 people found this helpful
              • 4. Re: Calculate rate within index column
                Kai Peng

                Hi Don,

                Thank you so much for spending the holiday hour doing this! I really appreciate it.

                I ended up doing something very similar yo yours. I exported the data with the index function. And then I group by the index number (since the index function restart every patient and the appointments date are sorted ascendant, the index# literally means patient's Nth visit)  in R to calculate the rate. I think pivot the index and visit-status column (either in tableau or excel) would also work in this case.

                I am closing the discussion since the answer leads me to the solution. Plus it accidentally did some of my future work .


                Thanks Don


                • 5. Re: Calculate rate within index column
                  Don Wise

                  Thanks Kai, glad to have at least partially helped! 


                  Best, Don