Worksheet Filter AND / OR
Hi,
maybe I'm missing something, but I couldn't find anything in the NUX documentation that AND / OR logic works with filters in worksheets.
During an analysis, a user may want to show all items that have a deviation of -1000 OR +1000. In classic, this was possible via the function ALL / ANY.
I just tested the same in a worksheet, but it seems to allow only AND and I couldn't find a way around it (see picture 2).
The only option would be to create a boolean formatted line item with OR logic and filter on this. Is this correct?
I thought this is quite common during analysis, so I wonder if somebody knows how to solve it without creating a calculated line item.
Otherwise I will post an idea.
Best Answer
-
This would be a nice modification to the worksheet indeed.
If it is on the worksheet, you can also use the additional insight panels in order to set-up a view with worst and best performers.
Normally I would create an absolute deviation line item and put a percentage on top of that, but it depends on the use case. The absolute works nice when looking at forecast accuracy.
Kind regards,
Eije Wiersema
0
Answers
-
Correct, this would be also my general approach. Unfortunately, the specific use case requires more flexible boundaries on not always the same line items. This seems a bit tricky with the given features.
0 -
Best practice with filtering is to create a module holding all your conditions and roll this into a single boolean.
Use this boolean in your filter in the NUX.
Holding all your filter logic in a single module ensures your are able to audit your criteria more effectively and abides by the PLANS guidelines to model building.
1 -
Thanks, I'm well aware of the best practise. But as said, that's not really feasible for this specific use case. The variation of required filter possibilities is just too high.
That's why I'm looking at the built-in filtering features of the worksheet. I will post an idea for a new feature.
0 -
If you have a large number of filtering criteria I would still use a module to manage how these all interact.
It would be far more efficient and effective this way rather than setting up directly in the UX.
You could even publish the selection to the insight pane to enable dynamic filtering so an end user could select what they would like to view. You can not enable this functionality when setting up within the worksheet itself.
0 -
I fully agree with you, that's certainly how it should be done. Yet, this is not feasible for this specific use case.
The filter needs to be user based and work on all dimensions & line items of the reporting module.
The only way to make it user based is by including the user list into the filter logic. Making it also work on all dimensions and line items, requires at least one boolean based line item that includes everything, resulting in a module that is very heavy on the workspace size. In addition you will need modules that include the user settings and the filter logic.
All together, that's a lot of technical effort and cost required to re-create a simple filter that was no problem in Classic UX and certainly works in Excel..
0 -
Agree that the AND/OR functionality should be made available in the UX, it's sorely missing. We shouldn't need to jump through hoops to recreate what was present before.
The current filtering doesn't properly reflect where the filter is placed either. I placed a filter on FY20 & FY21 where Is Not Equal to 0 and it's referenced as a Line Item filter. No indication that it becomes an AND filter, quite misleading.
0
