topic Re: Need idea for control/filter... in QlikView

Need idea for control/filter...

Thu, 12 Nov 2015 16:52:38 GMT

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.

Re: Need idea for control/filter...

Colin-Albert — Thu, 12 Nov 2015 17:07:13 GMT

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

Re: Need idea for control/filter...

sinanozdemir — Thu, 12 Nov 2015 17:23:31 GMT

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.

Re: Need idea for control/filter...

Thu, 12 Nov 2015 18:47:13 GMT

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

Re: Need idea for control/filter...

Thu, 12 Nov 2015 18:49:02 GMT

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.

Re: Need idea for control/filter...

sinanozdemir — Thu, 12 Nov 2015 18:51:12 GMT

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.

Re: Need idea for control/filter...

Thu, 12 Nov 2015 18:58:59 GMT

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.

Re: Need idea for control/filter...

Colin-Albert — Fri, 13 Nov 2015 08:30:49 GMT

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,

Re: Need idea for control/filter...

Fri, 13 Nov 2015 13:03:02 GMT

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.

Re: Need idea for control/filter...

Fri, 13 Nov 2015 16:40:06 GMT

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.