topic Re: set analysis: same Medication across multiple Visits in QlikView

set analysis: same Medication across multiple Visits

Margaret — Fri, 14 Oct 2016 15:13:20 GMT

Patients take medications on an ongoing basis (even when they are at home and not in the hospital), and in our system, those medications are represented by the same Home Medication Order (OmOrdID) across multiple Visits (VisitIDs). (See image below.)

I need to count how often step 2 in the HomeMedication process (AuditDate2) happens on the same date that a particular Document is Dated (DocumentDate). I need to count this for the individual providers that dictated (Dictator_ID) the Documents and I also want to look at all the providers (Dictator_ID's) that are in the same group (GroupName) at once, so I can see how they compare to each other.

I have a chart with a dimension:

Dictator_ID.

And I have this expression:

=Count ({$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID)

When I select, for example, Dr. Jane, the result is 46. But when I select Dr. Jane's GroupName (Hospitalists), the result for this expression in Dr. Jane's row is 45. This is because the patient was seen twice. Dr. Jane was the Dictator for one of the visits and Dr. Sue was the Dictator for the other visit. In both instances, DocumentDate = AuditDate2 is true.

But since Home Medication Orders (OmOrdID's) stay the same across multiple visits, I'm thinking it counts the OmOrdID twice (46) when only Dr. Jane is selected. But when the Group that both Dr. Jane and Dr. Sue belong to (GroupName = Hospitalists) is selected, it counts the OmOrdID once for Dr. Jane (45) and once for Dr. Sue.

So I need an expression that results in 45 no matter whether Dr. Jane is selected or Hospitalists are selected. I think that means I have to tell it to look at distinct VisitID's. But how do I do that?

Thanks in advance for any help you can provide!!!

Re: set analysis: same Medication across multiple Visits

sunny_talwar — Fri, 14 Oct 2016 15:16:16 GMT

Did you try adding DISTINCT may be?

=Count(DISTINCT {$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID)

Re: set analysis: same Medication across multiple Visits

Margaret — Fri, 14 Oct 2016 15:19:38 GMT

I did try that, and it didn't work. I think because the OmOrdID's are the same. But the VisitID's are different. So maybe if I could get that in somehow...

Re: set analysis: same Medication across multiple Visits

sunny_talwar — Fri, 14 Oct 2016 15:23:02 GMT

How about counting the VisitID then?

=Count(DISTINCT {$<OmOrdID = {"=DocumentDate = AuditDate2"}>} VisitID)

Re: set analysis: same Medication across multiple Visits

rupamjyotidas — Fri, 14 Oct 2016 15:25:43 GMT

Maybe use Aggr

Aggr(Count(DISTINCT {$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID),Dictator_ID)

Re: set analysis: same Medication across multiple Visits

Margaret — Fri, 14 Oct 2016 15:33:57 GMT

So that helped--it gave me consistent numbers no matter my selections. But it counts the number of visits for which the DocumentDate and AuditDate2 dates match. But there are multiple OmOrdIDs per visit. I don't know if it counting when ALL OmOrdID's have the same DocumentDate and AuditDate2? Or ANY of the OmOrdID's have the same? And they really want to know numbers by Medication, not by Visit.

Re: set analysis: same Medication across multiple Visits

sunny_talwar — Fri, 14 Oct 2016 15:41:18 GMT

I am not really sure I understand your data well enough to offer much help. May be look at that rupamjyotidas‌ have provided

Re: set analysis: same Medication across multiple Visits

Margaret — Fri, 14 Oct 2016 16:03:01 GMT

that gave the same results as the original expression 😕

Re: set analysis: same Medication across multiple Visits

Margaret — Fri, 14 Oct 2016 22:46:51 GMT

To put it another way: For each distinct VisitID, how many of the AuditDate2's belonging to each OmOrdID's have the same date as the Document date.

(The OmOrdID's have a one-to-one relationship with AuditDate2's, but only within VisitID's. For now, there is only one document for each Order ID. And as mentioned, there can be multiple OmOrdID's for each VisitID.)