Top Level Aggregation while Using Line Item Filters

akprasa1
Contributor

Top Level Aggregation while Using Line Item Filters

Hi Anaplanners ,

 

I would appreciate if someone in the forum could help me to resolve the below modelling issue that i have.

 

I have 4 dimension on which the business needs to do their planning ,forecasting process- Segment, Legal Entity, Department and Account. These dimensions are independent of each other and don't have a parent-child relationship.

When i start to build a module with these dimensions along with Dummy Versions and Time the model size blows up heavily.

 

I then started to create a combination list from code of these dimension to hold my values from Source and provided Line item filters for Users to see the data based on their Selection. Users could see the data based on whatever they select expect that when they select the Top Level items the data does not aggregate.(see Screenshot 1 and 2)

 

Can someone help to find a workaround for this?

Thanks in Advance

 

 

 

3 REPLIES 3
ChrisAHeathcote
Community Boss

Re: Top Level Aggregation while Using Line Item Filters

@akprasa1 

You need to enable the filter to resolve to TRUE when the top level list item is selected. 

Therefore, create an IF THEN ELSE formula for each dimension which queries whether the selection equals the top level and if so returns a TRUE before determining if it equals any other item in the list.

For example;

ChrisHeathcote_0-1624287151342.pngChrisHeathcote_1-1624287168970.png

 

The outcome will always generate a TRUE outcome if the top level item is selected otherwise the result will equal the value of the list item selected. If the selection is blank the result will be FALSE for all list items.

ChrisAHeathcote
Community Boss

Re: Top Level Aggregation while Using Line Item Filters

If you need the outcome of the selection to be user dependent then you will need to include the user list as a dimension in the final filter and filter selection modules. 

Use the 'current user' function in the filter settings when applying the filter to the target module.

akprasa1
Contributor

Re: Top Level Aggregation while Using Line Item Filters

Thanks Chris for taking the interest in answering. Really Appreciate your efforts.

 

I tried your approach, but i couldn't get the results as expected. May be the complete picture was unknown to you.

Let me explain the full picture: Step by step with screenshot

1.Imported data from Data Hub to spoke model in a module dimension by Combination Key (segment_Legal Entity_Deptt) and Account L3 (screenshot: data from Hub)

2.Created SYS Module to filter dimensions  that was published for User to Select the dimension(screenshot :Filter Selection)

3..Created a SYS module for Filter/Combination Properties and applied the logic you provided last on Boolean line items(Screenshot: Filter Properties/& Formulas)

4.Applied these Boolean filters to a consolidated module with an expectation that numbers would aggregate based on the User Selection.(Screenshot : Consolidated module)

 

Regards,

Arun