Set Analysis with OR (+) Operator AND Ignore Selections

Mon, 30 Jan 2017 20:25:05 GMT

Hi all. Ultimately what I want to do is count Field_5 under certain conditions (see below) while ignoring selections made in Field_1 and/or Field_2 lists. NOTE: The Conditions below include 'OR'

CONDITIONS:

Field_3 = 'Value'

Field_4 = 'Value'

Field_1 = BLANK or 'Value'

Field_2 = BLANK or NOT 'Value'

HERE IS WHAT I'VE TRIED:

ATTEMPT 1:

=Count(

{<Field_3={'Value'},Field_4={'Value'},Field_5={"=Len(Trim(Field_1))=0"},Field_5={"=Len(Trim(Field_2))=0"}>

<Field_3={'Value'},Field_4={'Value'},Field_1={'Yes'},Field_2-={'Value'}>}

DISTINCT Field_5)

ATTEMPT 2:

=Count(

{1<Field_1=,Field_2=,Field_3={'Value'},Field_4={'Value'},Field_5={"=Len(Trim(Field_1))=0"},Field_5={"=Len(Trim(Field_2))=0"}>

1<Field_1=,Field_2=,Field_3={'Value'},Field_4={'Value'},Field_1={'Yes'},Field_2-={'Value'}>}

DISTINCT Field_5)

THE PROBLEM

The count works as I need it to under both 'Attempts' (above), until I make a selection in Field_1 and/or Field_2 lists.

Re: Set Analysis with OR (+) Operator AND Ignore Selections

jonathandienst — Tue, 31 Jan 2017 06:05:57 GMT

By BLANK, do you mean null or empty string?

Selecting empty strings is easy enough, but nulls are a problem and not directly selectable in set expressions (which work the same way as user selections).

Lets assume for now that BLANK is an empty string:

Count({<

Field_1 = {'', 'Value'}, // BLANK or Value

Field_2 -= {'Value'} // BLANK is also in Not('Value')

Field_3 = {'Value'},

Field_4 = {'Value'},

DISTINCT Field_5)

You cannot include a field more than once inside a set qualifier, but the above will ignore selections in those fields - but it will NOT prevent data reduction by selection in other fields. So you may want to include overrides for those other fields.

topic Set Analysis with OR (+) Operator AND Ignore Selections in QlikView

Set Analysis with OR (+) Operator AND Ignore Selections

Re: Set Analysis with OR (+) Operator AND Ignore Selections