Total count is not equal to sum of rows

[#1]

Hello everyone,

I am completely stuck, and could really use some help.  My business case is that I need to create a Straight Table chart that counts the total number of distinct patients by practice that have had charge transactions over a rolling 12 months.  This needs to be calculated for a specific region, and it seems simple enough, but there is a catch.

The catch is that a patient could have charges at multiple practices within the region, but they should only count once based on their assigned "home" location.  Before selecting any dimensions in my table, I created the following formula to determine that my total count should be 166,899:

count({$<Region={'North'}, Year=, MonthName=, [Month Counter]={'>=$(=min([Month Counter]))<$(=min([Month Counter])+12)'}, Patient_Transactions.category_id={*}-{28,29,30}, Patient_Transactions.trans_code={'>=100'}>} DISTINCT Patients.unique_id)

This formula works, and I have been able to verify that the total is accurate.  Now, when I add my "Practice" dimension to the chart, I am able to get count totals by Practice, but the sum of my rows now equals 169,037.  This tells me that this chart is totaling the distinct count by Practice, and not overall.

Basically, I am working with 3 source tables: Patients, Locations and Patient_Transactions.  My patients' home location is stored in a field titled "Patients.location_id" in the Patients table, but this field only contains a numeric value, and I need to display the Practice name from the Locations table.  When I add AGGR( . . . , Patients.location_id) to the formula above, I get a table that only displays a couple of Practices, and a total of 51,953.  Based on my limited knowledge, I can only assume that this result is due to my one to many and many to one relationships that are stored in the script.  Any other variations that I've tried with AGGR, such as AGGR with Patients.unique_id, AGGR with Practice, AGGR with Patients.location_id, Practice either return values of zero, one or 169,037.

I am obviously missing something here, and unfortunately I am not able to upload an example due to the sensitivity of the patient information contained in my application.  With what I've written above, can you see anything that I might be missing?  Please feel free to let me know if you need more information about my setup.

Thanks for your help,

Jason

[Anonymous]

Jason

That is a long question to read & fathom out.

Would you be able to somehow distill your question down the to core of the issue and share a sample qvw with some sample data loaded ?

Your patient related data sounds like it would be unwise to share it on a public forum as is.  This document Preparing examples for Upload - Reduction and Data Scrambling by Rob Wunderlich   may help you make it suitable for sharing

Best Regards,     Bill

[Josh_Good]

I would recommend you approach this by flagging each patient if they had a valid transaction.  I will call it "VisitWithin12MonthsFlag".  So all the patients will get flagged with transactions in the last 12 months.  For each patient you should also have a field for Home Practice. 

You can then setup a table with Home Practice as the dimension and Count(Distinct {$<VisitWithin12MonthsFlag = {1}>} PatientID)

This will count all the unique PateintIDs who have had a transaction at ANY practice and summarize them by Home Practice.