One Disting Count Field Effecting Totals in Other Distinct Count

[jaybar48]

I have a report that works with healthcare data links 3 to produce 4 distinct totals using the Running Total Wizard. wizard.

Distinct Total A is the number of active clients who had any type of encounter during the report period. The report return a value of 1930 which is correct.

Distinct Total B is a subset of A and is the number of clients who had a Primary Care encounter. The report returns 1520, which is correct.

Distinct count C is a subset of B, which are the number of clients who have Medicaid as their primary insurance. When I add this distinct count to the report, the values of A and B are lowered


Since I am not using record selection, why would distinct counts for A and B be lowered by adding distinct count C. I would think they would be independent.

I am doing my distinct counts by selecting the formula evaluation option in the wizard and creating a boolean formulula. Is there a better way? What might be happening here?

Thanks,

Jay

 

[lbass]

If the Medicaid field comes from a different table than the encounters field, you need a left join from the main or encounter table to the table that holds the Medicaid field. If this is not the case, please provide a little more information about your tables, fields, etc.

-LB

 

[jaybar48]

Thanks LB, the left joins seem to have worked. My question now is why? If you are using running totals with formula evaluations are you not just counting records. Why should introducing one distinct count have a bearing on any of the other distinct counts.

Thanks,

Jay

 

[lbass]

My guess: I think that the equal join to the table containing the Medicaid field had no impact UNTIL you introduced a field from that table--and you did that when you created the running total. Equal joins will only show those records where there is a match on your linked fields, while a left join allows there to be a record in your left table without a corresponding record(s) in your right. If you started out with an equal join, but were only using fields from the left table, the join was irrelevant--only when you tried to use a field from the right table did the match get checked between table fields. The left join links the left table to the right, but without requiring a matching record in the right table.

So when you added the running total field, your total record count decreased to correspond to the number of matched records. This would affect other running totals because the pool (total count) of records had now changed.

-LB