Filter on aggregated values

[IvoMoor]

Would be nice to be able to filter on an aggregated value (like != 0)

[rpbouman]

There are multiple challenges to consider:

• how will this work in the UI? After some thought I think it would be most intuitive to give the aggregate functions a filter icon in the attribute UI, just like it works for the non-aggregate attributes. Clicking the filter would also add the aggregate item to the filter axis in the query model, just like it works for non-aggregate items. With this setup it is also possible to have a normal "WHERE" filter on a particular column while simultaneously having a filter on some aggregate function on that column. The distinction would be because of the presence of the aggregator, and the user would not need worry about that.
• the real challenge is how to implement this on the query level. It seems clear that filtering of aggregate functions is separate from filtering on non-aggregated items: non-aggregate item filters should be pushed down to the axes and affect the existence of tuples on the rows and columns axes. Filters on aggregate functions can only be applied on the cells axis, and they would be implemented using HAVING. The interesting thing is that when we render the cells, the tuples have already been rendered. If we find out that a particular row or column tuple does not have any cells at all, then it would be expected that those rows and columns would not be visible. But at that time, the rows and column have already been rendered. Eliminating/hiding them from the grid by the time the cells are calculated would mean the grid would only get partially populated, but there would be an expectation that the grid would aways be completely filled out.

[rpbouman]

After some thought I think this should initially be implemented as a feature for a subclass of queries/reports:

If there is only one tuples axis (rows or columns) and the other axis is empty, then we can fold the cells and tuples query into one SQL query and use HAVING to actually restrict the resultset. (Also, see: https://github.com/rpbouman/huey/issues/21)

The trouble starts when we have 2 axes - in that case, it is impossible to know whether a particular row or column tuple should be hidden because all its cells don't satisfy the condition without executing the query for all possible tuples (along both axes). In other words, that requires execution of the entire crosstab dataset. (we're not going to support that any time soon)

[rpbouman]

If we accept the limitation of having only one tuples axis, then we need only worry about how this would work in the UI. There are two states where we need to take care:

• 1 axis populated and aggregate items appear on the filter axis. In this state no items may be placed on the "free" axis
• 2 axes populated. In this state, no aggregate items may be placed on the filter axis

We can think of two different UI styles:

• "lock": the Ui visually indicates that the axis is locked, item placement buttons for restricted placements appear greyed out or even hidden etc. Drawback of this approach is that the user may not understand why the UI blocks certain features
• "warn": the Ui doesn't change, but when a restricted placement is initiated the app warns the user. This would have to be a modal interaction where the user is forced to choose how to proceed: either choose cancel, thus maintaining correct state, or choose to proceed, which would then necessarily remove the items with incompatibly placed items.