Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis with OR (+) Operator AND Ignore Selections

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.

1 Reply
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein