
1. Re: Calculate rate within index column
Don Wise Dec 26, 2018 1:38 PM (in response to Kai Peng)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

No Show Rate.twbx 409.5 KB


2. Re: Calculate rate within index column
Kai Peng Dec 26, 2018 2:17 PM (in response to Don Wise)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!
best,
Kai

3. Re: Calculate rate within index column
Don Wise Dec 27, 2018 11:57 AM (in response to Kai Peng)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:
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).
Duplicating the table and then representing the % of each visit against the Total # of Appointments:
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.

NoShowTest.twbx 467.3 KB


4. Re: Calculate rate within index column
Kai Peng Dec 27, 2018 1:20 PM (in response to Don Wise)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 visitstatus 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
Kai

5. Re: Calculate rate within index column
Don Wise Dec 29, 2018 8:12 AM (in response to Kai Peng)Thanks Kai, glad to have at least partially helped!
Best, Don