Calculation Functions

NancyKhurana
Occasional Contributor

Calculation Functions

Hi , I'm facing an issue with the Selector in the below image . 

 

In Grid 1 , when I select Project Name = DARE , Product = Integration and Specifics = BU, I can see all other combinations as well which has these selected values whereas I just want to see the one highlighted below in Grid 2 . 

Grid 1 has List drop down line items of a different module and Grid 2 has dimension as Unique Key. I have to apply Boolean filter to show all rows with selected Project /Product/Type & Species...any selection can be blank and grid 2 should show rows combination based on above selection only.

 

What formula would will be applied to eliminate the rest of the combinations !!? hgjh.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Calculation Functions

@NancyKhurana 

It you need to create a filter module or line item where you can query the value selected in the first grid with the property held within each line item. 

To allow the user to display exactly what is selected you will need to create a logic in which a blank entry is always TRUE and will result in all items for that list showing in any filter.

Create a boolean line item in the second module for each of the lists in the first grid.

Call these line items - 'List Filter 1 - 4'.

For each line item use the following logic;

=ISNOTBLANK(FirstGrid.LineItem) OR SecondGrid.LineItem = FirstGrid.LineItem

This will return a TRUE outcome when the list item in the second grid matches the selection or the selection grid is left blank.

Add a final boolean line item. Call this 'Final Filter.'

=SecondGrid.LineItem 1 AND SecondGrid.LineItem 2....so that all line items in the second grid are covered.

 

Use this line item to inform a filter in the module. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

2 REPLIES 2
andrewtye
Master Anaplanner/Community Boss

Re: Calculation Functions

For each would have a boolean line item along the lines of:

isblank(grid1. line item) or grid1. line item=grid 2.line item

And then a line item that goes 1 and 2 and 3 and 4 - then filter on that one.

Hope that helps!

ChrisHeathcote
Community Boss

Re: Calculation Functions

@NancyKhurana 

It you need to create a filter module or line item where you can query the value selected in the first grid with the property held within each line item. 

To allow the user to display exactly what is selected you will need to create a logic in which a blank entry is always TRUE and will result in all items for that list showing in any filter.

Create a boolean line item in the second module for each of the lists in the first grid.

Call these line items - 'List Filter 1 - 4'.

For each line item use the following logic;

=ISNOTBLANK(FirstGrid.LineItem) OR SecondGrid.LineItem = FirstGrid.LineItem

This will return a TRUE outcome when the list item in the second grid matches the selection or the selection grid is left blank.

Add a final boolean line item. Call this 'Final Filter.'

=SecondGrid.LineItem 1 AND SecondGrid.LineItem 2....so that all line items in the second grid are covered.

 

Use this line item to inform a filter in the module. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post