Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
Thanks. That seems to select or deselect only 3, 6, 9 and 12 for some reason...
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.
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.
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.
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,
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.
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.