Count of Unique Participants in a Time Range
Hello,
I am attempting to write a formula for this sheet that counts the total numbers of unique patient ID within a date range (7/1/20-6/30/21) from the "date of CM" column. Anyone know how to do that? I keep getting an error message.
Answers
-
That would look something like this:
=COUNT(DISTINCT(COLLECT([Patient ID]:[Patient ID], [Date of CM]:[Date of CM], AND(@cell>= DATE(2020, 07, 01), @cell<= DATE(2021, 06, 30)))))
-
@Paul Newcome Thank you that worked perfectly! One more please? Now that we have the total unique patients, they often come more than once so i want to tally the total number of patient encounters across all patients in the time frame of "date of CM" 7/1/20 - 6/30/21?
-
In that case you would use a straightforward COUNTIFS and only use the date range and criteria set that we used in the last formula.
-
Hi Paul,
That worked as well you're a magician! One more on this same sheet. I created a new one for the next fiscal so we can archive databases each year and put older data sources on secure offline drive. So since this new data base already distinguishes by date range, I removed everything else in the formula but it's coming up as "1" which just isn't true so I'm left to believe I messed something up in the formula. Here's what I tried and not getting "1" for. We are using MRN as the distinct ID not Patient ID this time.
=COUNT(DISTINCT(COLLECT(MRN:MRN)))
-
If you do not need to "filter" the results by date and just need that single column to be evaluated, you wouldn't use the COLLECT function.
=COUNT(DISTINCT(MRN:MRN))
-
@Paul Newcome - the formula is still giving me a zero even when i changed it to remove the collect function. There are over 20 patient MRN IDs in here. Any ideas on why it's giving me an incorrect value?
-
What type of data do you have in that column? I see you mention IDs. Are they all numerical? Are there some with leading zeros? Are there some that are numerical and some that also contain letters?
-
@Paul Newcome it is all numerical values
-
And none have any leading zeros?
-
@Paul Newcome Hello Paul - currently none of the of the ID# lead with zero.
-
Let's try this...
Insert a helper column into the source sheet (can be hidden after setting up) and use this column formula:
=MRN@row + ""
(plus quote quote)
Then reference this helper column in the COUNT/DISTINCT.
-
@Paul Newcome What is a helper column? What will happen in the help column that will resolve the formula error? Thanks for explaining this! I'm not the only person interacting with this sheet so i want to be ale to explain what's happening to my team and why we are doing it this way.
-
A helper column is a column that helps manipulate the data so that various other actions can be completed based on the data. Helper columns can usually be hidden after setting them up as they are typically only for "back-end" functions.
This helper column in particular converts all entries in the MRN column into text strings. The reason for this is that the way things are (or aren't) working right now tends to indicate different data types in the source data (some entries being stored as text strings and other entries being stored as number strings). Converting them all to text strings using the above method provides for data type consistency which helps with various functions and formulas when evaluating the source data.
-
@Paul Newcome Great - thanks for the explanation! How do you create/edit a column formula?
-
Here is some info on column formulas:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!