Worksheet Filter AND / OR

Highlighted
Certified Master Anaplanner

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.

 

PhilippErkinger_1-1603808890058.png

 

PhilippErkinger_0-1603808801751.png

 

Planning Rockstar at Bluesprint
6 REPLIES 6
Highlighted
Master Anaplanner/Community Boss

Re: Worksheet Filter AND / OR

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

Highlighted
Certified Master Anaplanner

Re: Worksheet Filter AND / OR

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.

Planning Rockstar at Bluesprint
Highlighted
Super Contributor

Re: Worksheet Filter AND / OR

@PhilippErkinger 

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.

 

Highlighted
Certified Master Anaplanner

Re: Worksheet Filter AND / OR

@ChrisHeathcote 

 

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. 

 

Planning Rockstar at Bluesprint
Highlighted
Super Contributor

Re: Worksheet Filter AND / OR

@PhilippErkinger 

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.

Highlighted
Certified Master Anaplanner

Re: Worksheet Filter AND / OR

@ChrisHeathcote 

 

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..

Planning Rockstar at Bluesprint