Count with AND & NOT Selections

[shyamcharan]

Hi Experts,

I have a AND-mode selection requirement where I have to implement AND-mode in List Box as in here

Just a little description on this:

If I have patients Sam, John, Antony and Alex as show in the data below.

I need to show a patient who had multiple drugs at a time but not just the one selected.

For example: If I want to see a patient who had Drug2 AND Drug5, I should see John and Antony only

but not Sam or Peter(though they had Drug2 OR Drug5, my requirement is to show only those who had both the drugs)

  EPCRID, Patient, Drug

    12345, Sam, Drug1

    12345, Sam, Drug2

    12345, Sam, Drug3

    12345, Sam, Drug4

    54321, John, Drug2

    54321, John, Drug5

    98765, Antony, Drug2

    98765, Antony, Drug4

    98765, Antony, Drug5

    22222, Peter, Drug4

    22222, Peter, Drug5

    99999, Alex, Drug4

To acheive this I have implemented the AND-mode join in the load script. Please find the attached QVW.

However, now my requirement is

1) To show count of patients with selected drug. in this case the count should ignore the NOT(excluded brown color) selections

For example: AndMode Selection = &Drug2, !Drug5 Result = Count of patients who had Drug2

2) To show count of patients with selected drug and considering not having excluded(NOT) drug

For example: AndMode Selection = &Drug2, !Drug5 Result = Count of patients who had Drug2 but did not had Drug5

Can I achieve this? Thanks in advance.

Shyam

[sunny_talwar]

It seems like you still want the and search for the two selections you make and not or. So, essentially you want people who have both drugs and not people who have at least one of the two drugs, right? Will see if there is a way to do this.

[sunny_talwar · Accepted solution]

Try this

=Count(DISTINCT {<Patient = {"=Count(DISTINCT {<Drug = {$(=Replace(RTrim(Replace(Replace(Replace(SubField(GetFieldSelections(andDrug), '!', 1), ',', Chr(39) & ',' & Chr(39)), '&', Chr(39)), ',' & Chr(39), '    ')), '    ', ','))}, andDrug>} Drug) = GetSelectedCount(andDrug)"}, andDrug>} Patient)

[shyamcharan]

Thanks heaps sunny. Tussi great ho!!!!

It works like a charm.

A BIG thank you.

[sunny_talwar]

I am glad it finally did