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

[jonathandienst]

>>But, how can i get count based on the logic

Count(Distinct EPCRID)

I don't think there is any way to override the Not selection only. Set analysis overrides the selections on a field, but it does not differentiate between the individual parts of an AND selection. The counts could be like this'

Count({<EPCRID = P({<Drug = {'Drug2'}>} EPCRID)>} Distinct EPCRID)

or Count({<Drug = {'Drug2'}>} Distinct EPCRID)

Count({<EPCRID = P({<Drug = {'Drug2'}>} EPCRID) * E({<Drug = {'Drug5'}>} EPCRID)>} Distinct EPCRID)

[tresesco]

If you use Count( Distinct EPCRID ) that should take care of the cases when a patient would have multiple visits. However, for first requirement: I guess it would be difficult (if not impossible) to ignore the 'NOT Selection'. Because I don't know a method to separate out 'AND Selection' and 'NOT Selection' here. You can probably use alternate state to handle this requirement. Refer vgutkovsky‌'s proposed solution for a clue in the post you referred.

[sunny_talwar]

May be this for the first req:

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

Basically, I tried to trim down your selections and not selections

selections -> &

not selections -> !

From there onward it was mostly getting it into a format which can be used in set analysis.... may be there is a better way to do this string manipulation, but this was the best I was able to come up with..

Best,

Sunny

[tresesco]

Did not work much on NOT and AND mode selections. I never realized that getfieldselections() could return the field values with '&' and '!' indicators. That's a good notice and solution accordingly.

However, when I try few things with this, it looks very different in behavior (other than the selection logic).

GetSelectedCount(Field) for one &selection and one !selection gives 1 as output

while Count(Distinct Field) gives 4

Could you explain this behavior ? Sorry, if this is already documented somewhere, I did not come across.

[sunny_talwar]

AFAIK Count() function usually counts all the possible (and available) selections. In this case Count(DISTINCT andDrug) is counting Drug1, Drug2, Drug3, and Drug4. Whereas GetSelectedCount(), counts this which are directly selected in the field. In this case, although we have two selections (selection and not selection), it appears to be counting the selection only, I believe (don't know why).

The best case to see the difference between Count(DISTINCT) and GetSelectedCount() is when no selection has been made. Count(DISTINCT) will count DISTINCT will show the distinct count of the field, whereas GetSelectedCount will show 0 because you have not selected anything in the field.

I am not sure if I answered what you asked for, but even I am not 100% sure about the behavior. May be hic‌ can share his insights.

Best,

Sunny

[hic]

Sunny's answer is pretty much it.

Count(distinct ...) counts all possible values - which is different from the selected ones. A field value can be selected without being possible. See Colors, States and State vectors.

Then we have the field functions that operate on the symbol tables and count the values in the different states :

GetSelectedCount()

GetAlternativeCount()

GetPossibleCount()

GetExcludedCount()

GetNotSelectedCount()

Of course, the GetSelectedCount only counts the the positive (green) selections. To count the red ones you should use GetNotSelectedCount.

HIC

[shyamcharan]

Hi Gurus,

Thanks a ton for all your responses in helping me find the solution.

It a great help for me indeed.

Sunny, thanks heaps for the formula. I am going to try it out and will post the output.

Appreciate a lot.

Sincerely,

Shyam

[shyamcharan]

Hi Sunny,

Thanks again for your time and help here.

I have tried your formula and it is working well when there are AND & NOT selections in the andDrug field.

But, it does not work when NOT selection is not made.

Is it how it will work?

Thanks in advance.

[shyamcharan]

Hi Sunny,

Sorry, it looks like its not working when I select two AND values (along with a NOT) as shown in the attachment.

In the attached QVW, I have selected Drug2 AND Drug4 along with a NOT Drug3.

The result ignoring the NOT selection should be = 2 as there are two patients sam and Antony who had both the drugs Drug2 AND Drug4.

But the output is 5.

Any idea how can this be fixed?

Thanks again.

[tresesco]

Thanks Sunny, HIC,

Now I notice that even if one is selected (green) few are there in white (possible). This is not the case very often we see in general selection and so I got confused. And yes - the GetNotSelectedCount() answers and clears the actual doubt. Thanks you both once again. Few things are clearer now.