Need idea for control/filter...

[#1]

Given a large data set, I have one column descriptively called "Mos Since Last Movement" which, for a given item, indicates the last movement occurrence of that item. The users have a need to filter their charts and tables based on this column. I could just put a listbox out there, which would effectively be a list of integers from 0 to around 36, but this is a cumbersome way to filter, and they don't need to be so granular.

What's desired is a control that says show me only items with movement in the last 3/6/9/12 months -- and show me items that haven't moved in the last 3/6/9/12 months.

One caveat: A null value indicates there's no recorded movement of an item. I could dummy up a value (like 999) but this is ugly and potentially misleading. They would also want the filter capable of showing just these items (equivalent to selecting null in the filter).

Ideas? I have a few but they feel awkward. Thanks.

See attached for a sample data set.

[Colin-Albert]

You could add a slider object to set a variable and then use the variable to set a Dimension limit on the last moved value. The slider can be set to increment 3,6.9.12

[sinanozdemir]

Hi Brian,

I think Collin's idea is the best, but I did something similar and I had three buttons showing included items that are entered into an input box as well as excluded items and null/blank items:

Hope this gives you an idea.

[#4]

Thanks. That seems to select or deselect only 3, 6, 9 and 12 for some reason...

[#5]

I'll work with the slider object. Since I'm applying the filter to an expression and not a dimension, I'm not sure I can use the dimension limits. Maybe I'm not understanding.

[sinanozdemir]

You can type in the input box what you want to select such as 3/6/9/12 or 4/9 and the other button will show the excluded items. I thought that's what you were looking for.

[#7]

Sorry if I wasn't clear. The control should filter on Months_Since_Last_Movement as follows:

>=3*

>=6*

>=9*

>=12*

<=3

<=6

<=9

<=12

The options with * should include null values. I could sub 999 for null if the values won't be displayed anywhere and it makes the filtering easier.

[Colin-Albert]

The easiest option would be to add the MonthsSinceLastMovement field to your data model and set nulls to a boundary value say 999, then the filters  become simple.

It may be worth excluding new products from the field so you can differentiate between a recently added product that has no movement and something that has sat on the shelf for years,

[#9]

Thank you . I can certainly do that (convert null to 999). I still need an idea for a user friendly way to present this filter. I had been exploring buttons with the "select in field" action, but I don't think I want eight or more buttons to manage the options shown. I don't see how to get a slider work to select values greater than/less than the selected value.

[#10 · Accepted solution]

So here's what I've done. I created an inline (island) table with the filter selection options of interest:

_index, _label

0, Show all items

3, Show items with movement in last 3 months

6, Show items with movement in last 6 months

-3, Show item with NO movement in last 3 months

[etc.]

I added a listbox for _label and a Select In Field trigger action with OnSelect from this listbox. The trigger selects values in a hidden Months_Since_Last_Movement listbox using the following expression:

=if(_index = 0, '', if(_index < 0, '>' & fabs(_index), '<=' & _index))

No variables to manage, no expressions to change. It seems to work. 'Show all items' is the default and one values is always selected.

Thanks everyone.