And/Or Subset of Objects

[#1]

Hello,

I am fairly new to QlikView, and I have a requirement I can't seem to figure out.

I need to give users the option to AND or OR a list of object selections.

Here's a simplified list of the objects:

Listbox - Drug

Listbox - DiagnosisCode

Listbox - Procedure

Date Range - Date

Listbox - Gender

(The first four are in a single table.)

I need to be able to allow users to choose whether or AND or OR the first three while always applying the last two.

The first three are each in their own alternate state group.

My plan was to create a variable that can be set to 1 or 0 to indicate whether the first three should be ANDed or ORed, and then use set analysis to generate the counts that are used in all of my graphs.

(All calculations are counts based on distinct member ID.)

I have a couple of issues. I cant' seem to figure out how to get a selection to be used in set analysis.

Here's a sample of what I was trying for the OR logic:

    count({$<Drug={Drug}>} DISTINCT MemberID)

    +

    count({$<DiagnosisCode={DiagnosisCode}>} DISTINCT MemberID)

(I realize that this is probably just searching for the literal names, rather than the selected values from the fields with those names.)

I'm also not sure how to apply all of the other filters to the set of ANDed or ORed items.

How can I say (for the OR scenario):

    Set of items filtered excluding Drug, DiagnosisCode, Procedure selections

    that also exist (intersect) with

    Set of items that contain selected Drug(s) OR DiagnosisCode(s) OR Procedure(s)

The AND scenario is (I think) simple, because that's just how QlikView works out of the box.

Thanks for any help!

Bret

[#2]

It occurs to me that I could do something like this (again, for the OR):

count({[MainState] * ([DrugState] + [DiagnosisState] + [ProcedureState])} DISTINCT MemberID)

Any reason this wouldn't work, if all of the "other" filters I wanted to apply were in the MainState alternate state?

[#3]

See if this is kind of what you are looking for.  I used island fields that mimic your true fields for the 'OR' option.  Let me know if anything doesn't make sense with it.  Hope it helps.

[#4]

It seems to be close, although I can't get the logic to work when applying it to a pie chart of gender while ORing (see attached file).

Thanks for your help, I really appreciate it.

Bret

[#5]

You need to add a Sum().  See attached.

[#6]

Thanks again.

My data is actually a little different than I explained. I was hoping to avoid confusion in my explanation, but the differences matter.

In the data table, a given member will only have either a drug, a diagnosis or a procedure.

Additionally demographic information about members is stored in another table. There are multiple records per member in this table because it contains other information about the period during which the member was covered.

Because of this structure, I've run into a couple of problems when trying to implement your solution (probably due to my ignorance):

• When using AND multiple ANDed selections cannot be selected
• When using OR, the counts are off (this isn't a problem with AND because I was able to add DISTINCT to COUNT)

If you're able to provide some information on how to apply your solution to my real schema, I'd be very appreciative.

I've attached an example.

Thanks so much!

[#7 · Accepted solution]

Okay, I added in a distinct to the count for OR selection.  Take a look and see if it helps at all.