Filter straight table results for zero, positive or negative

[Johnnyg248]

I have a sheet that has  a bunch of list boxes that the end user can use to filter data displayed in a straight table

One of the things they have mentioned is that they would like a way to chose to return records where

   1.  total amount is zero

   2. Total amount is positive

   3. Total amount is zero

(These choices are mutually exclusive.  i.e.  one time they want to see only zero,  a different time only positive, etc)

I was thinking of a list box containing Zero, Positive, Negative and then putting expression in straight table but not sure

how exactly this would work or if there is perhaps a better way ????

Thanks!

[marcus_sommer]

I think this: Positive-Negative-filter-in-a-bar-chart will be useful for you.

- Marcus

[Johnnyg248]

So would I store the end user choice from the list box in a variable?

[rwunderlich]

You could mark the column as "searchable" on the Presentation tab.  Then your users could use 0, >0, <0 as the search.  Note that expression search only searches within the currently filtered results. So if they wanted to toggle from >0 to <0 they would need to press clear first.

-Rob

[rwunderlich]

You could create a listbox with the expression:

=aggr(
if(sum(LineSalesAmount)=0, 'zero'
,if(sum(LineSalesAmount)>0, 'positive'
,'negative'))
,CompanyName)

updating for your expression and listing all Dimensions used in the chart.

-Rob