How to filter line items

myasuda
myasuda
in Modeling

Hi,

 

We have hierarchy type lists of Market Unit and Country Group (Market Unit is parent.) and Country Group list is specified as a dimension of a module. In the module, the line item 'B' is applied to Market Unit.('Applies to' of the line item 'A' is left default.)

 

In such a situation, if user selects one of the Country Group in the Page selector in the module, the line item 'A' only needs to be shown ('B' should be hidden.) When one of the Market Unit is selected, 'B' only needs to be.

How can we do this?

 

Best Regards,

Maki Yasuda

Tagged:

Best Answer

  • myasuda
    myasuda
    Answer ✓

    Hi,

     

    Your advice is very helpful for me.
    I also understand that 'Summary' settings is important for accurate calculation.

    Thank you for your support!

     

    Best regards,

    Maki

Answers

  • Egor
    Egor

    Hi Maki,

     

    I doubt that you can filter on line items depending on value of another line item, however in your case I would try this:

     

    1) Create a line item to control for parent list items vs child list items.

        Simly type "1" in formula field an it will automatically sum for parent items

    2) Create a new line item ex. "Value to show" and write an IF formula so it would equal Line Item A for      parent items and Line Item B for child items

     

    See screenshots attached for details.

     

  • myasuda
    myasuda

    Hi,

     

    Thank you for your helpful advice and screenshots attached.

    However, 'Applies to' column in blueprint of the line item 'B' is set to 'Market Unit'. On the other hand, one of  'A' is 'Country_Gourp' (left defalut). So IF formula you mentioned cannot be put because dimensions of 'A' and 'B' are different. Error message has been shown as the attached screenshot.

     

    Thanks and regards,

    Maki

  • Egor
    Egor

    Hi Maki,

     

    Good point. In this case I would suggest creating a new line item (ex. Line Item B Drill Down) to bring line item B down to the default Applies to. Hope this will not significantly influence your model size.

     

    The screenshots of the solution are in attachment. Note that Summary should be Max or Min or Average for "Line Item B Drill Down" and Formula for "Value to show".

Categories