Forum Discussion
Tracking for patients
I'm working on a project. I need to track the patient movements within 14 days if he/she went to same service within 14 days so it will appear with different color as the sheet contains around 16k member which is too hard to track.
20 Replies
I removed the Specific Assessments search box since it seems to make excel crash when left empty, and when both drop downs are empty excel will just stop responding
I was looking at the tool and made some improvements on it for example:
If you have a large hit, a card number with a long list
I made a pivot table so when you have something like this you can switch to pivot table view and immediately pinpoint what you need to look at:
when you're doing a search in the search sheet you press ctrl+alt+F5
when you go to the pivot sheet to update the pivot table press ctrl+alt+F5
I'm not sure why it does not update the pivot table when ctrl+alt+F5 should update queries and pivot tables, you would have to inquire with uncle MS about that
Also when no card is selected, i set it up to search by default a card number that is
0000-0000-0000-0000
you would have to insert that record at the end of the report list in the Patients sheet
like so:
MarinaSaadalla Here's a tool that might be helpful. You enter the Card Number and it will list all transactions related to the card:
Hope that helps you in your project.
Cheers
- Can you help me to understand what you did i'm trying to do it again but i fail
- I'll make instruction for you its a power query 2 tables, one main list table and the other is the variable table, the variable table is where you input your search criteria. I will make instruction so you can follow
- no to be consecutive will be better
I filtered it the old fashioned way much faster
I created 2 helper columns at the right most side to filter in:
1. any dates that are duplicates
2. by Assessment
Then you can spot check to see if anything interests further investigation or you can filter it by card to view the details of the multiple transactions on that same date. Let us know if that helps
MarinaSaadallaCould you share a sample of your data, without disclosing any private information? Would be helpful to see what you are working with, in order to provide a solution. A few hundred rows of data might be sufficient. And if you could indicate what summary you would expect to see for these would be even better.
this is a sample from my project.
as you can see we have the id no. and the service that the patient used with the cost, the date and the provider for the service.
what I'm expecting: to find the abuser/misuser of the service by the no. of visits to the provider, dates are near to each other or going for the same diagnosis with high no. of frequency anything weird i can find will help
MarinaSaadalla You send a nice file with over 80 thousand records. I cannot possibly oversee what kind of data you have in there. But perhaps a few tips to get started. You may want to split the P.O.ID (column Q) so that you have the PO number and the year. And then perhaps also split the Specific Assessment (column U) in Code and Description.
Then you could do a first summary (count and cost) by P.O. number (or whatever column identifies an individual), Assessment code and Date, using a pivot table. But the number of POs and Assessment codes is quite big, so perhaps you need start by counting the number of visit per PO per month. That could eliminate most of them. Then you focus on those POs that have many occurrences, as the others are probably "not weird". But that's for you to judge.
