Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with a list box expression

Hello,

I am having trouble with getting a list box expression to filter the way I'd like it to.

The expression I have is pasted below. 

=if(Field1= 'Yes' and Field2 = 'Yes','Yes','No')

Field1 and Field2 can either have a value of Yes or No.  If Yes is selected in the list box, I only want records that have a value of "Yes" for both Field1 and Field2 to display.  If either or both Field1 or Field2 have a value of No, I only want the record to display when the list box has "No" selected.

This chart shows what I'm looking for.

Field1Field2Should Display when
YesYesList Box is equal to Yes
YesNoList Box is equal to No
NoYesList Box is equal to No
No NoList Box is equal to No

Any help is much appreciated.

1 Solution

Accepted Solutions
Not applicable
Author

Well you could create a seperate field for that:

LISTBOX:

LOAD * INLINE [

listbox

Yes

No];

That way it isnt hardwired to the attributes. And then place a Select field on "listbox" on your sheet.

View solution in original post

6 Replies
Not applicable
Author

Don't have time to test it right now, but my guess is:

if( listbox = 'Yes',

Sum( {$<Field1={Yes},Field2={Yes}>} somenumber),        // Both fields yes

Sum( {($<Field1={No}>)+($<Field2={No}>)} somenumber)  // + unions selection of Field1=no and Field2=no

)

Hope this points you in the right direction,

Jeroen

Not applicable
Author

Thank you.  For the listbox = "Yes" part, how do I reference my listbox?  Do I need to reference it by the name? 

Not applicable
Author

Given that NO is the controlling element of the expression, I would have thought that the formula logic should be better written thus:-

If field 1 is no then put no, if field 2 is no put no otherwise put yes.

i.e.:-

IF([field1] = 'No', 'No', if([Field2] = 'No','No', 'Yes'))

Not applicable
Author

Well you could create a seperate field for that:

LISTBOX:

LOAD * INLINE [

listbox

Yes

No];

That way it isnt hardwired to the attributes. And then place a Select field on "listbox" on your sheet.

Not applicable
Author

When I try using the Listbox field with the separate field, it doesn't filter the report at all, regardless of whether I pick yes or no.  Do I somehow need to tie this new field to anything besides the listbox?

Thank you for your responses; I really appreciate your time.

Not applicable
Author

I ended up figuring it out, so you can disregard my last question.  Thank you for your direction!  I used the getFieldSelections() function to determine whether Yes or No was selected, and then used that to filter my chart correctly based on the values in Field1 and Field2.  Thanks again!