Help with a list box expression

[#1]

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.

Field1	Field2	Should Display when
Yes	Yes	List Box is equal to Yes
Yes	No	List Box is equal to No
No	Yes	List Box is equal to No
No	No	List Box is equal to No

Any help is much appreciated.

[#2]

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

[#3]

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

[#4]

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'))

[#5 · Accepted solution]

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.

[#6]

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.

[#7]

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!