Assume the following Non-Composite list, ragged hierarchy, needs to be set to Production Data.

01.png

We need to refer to the ultimate parent to define the logic calculation. In the example, we have assumed that children of Parent 1 and Parent 3 need to return the 'logic 1' value from the constants module below, and those under Parent 2 return 'logic 2,' and we apportion the results based on the initial data of the children.2019-04-18_10-41-17.png

2019-04-18_10-43-03.png

Select Proportion:

Data / IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' THEN Data[SELECT: 'Non-Composite List'.'Parent 1'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Data[SELECT: 'Non-Composite List'.'Parent 2'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Data[SELECT: 'Non-Composite List'.'Parent 3'] ELSE 0

 

Select Calculation:

Select Proportion * IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Parent Logic Constants.'Logic 1' ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Parent Logic Constants.'Logic 2' ELSE 0

 

These “hard references” will prevent the list from being set as a production list.03.png

SOLUTION:

Create a Parents Only list (this could be imported from the Non-Composite list).  As we don't need the sub-level parents, we do not need to include 'Child 3.1,' even though it is technically a parent.2019-04-18_10-51-30.png

To calculate the proportion calculation without the SELECT, a couple of intermediate modules are needed:

 

Parent Mapping module

This module maps the Non-Composite parents to the Parents Only list.  Due to the different levels in the hierarchy, we need to check for sub levels and use the parent of Child 3.1. In this example, the mapping is automatic because the items in the Parents Only list have the same name as those in the Non-Composite list. The mapping could be a manual entry if needed.

2019-04-18_10-57-32.png

 

The formula and “applies to” are:

 

Non Composite Parent:
PARENT(ITEM('Non-Composite List'))
Applies to: Non-Composite List

 

Parent of Non Composite Parent:
PARENT(Non-Composite Parent)
Applies to: Non-Composite List

 

Parent to Map:
IF ISNOTBLANK(PARENT(Parent of Non Composite Parent)) THEN Parent of Non Composite Parent ELSE Non Composite Parent
Applies to: Non-Composite List 

 

Parents Only List
FINDITEM(Parents Only List, NAME(Parent to Map))
Applies to: Parents Only List

 

Parents Only subtotals

An intermediary module is needed to hold the subtotals.
2019-04-18_11-01-10.png

Calculation:

Parent Logic Calc.Data[SUM: Parent Mapping.Parents Only List]

 

Parent Logic? Module

We now define the logic for the parents in a separate module.

Add Boolean line items for each of the “logic” types.


2019-04-18_10-53-33.png

Then you can refer to the logic above in the calculations.

2019-04-18_11-04-27.png

Lookup Proportion:

Data / Parents Only Subtotals.Calculation[LOOKUP: Parent Mapping.Parents Only List]

 

Lookup Calculation:

Lookup Proportion * IF Parent Logic?.'Logic 1?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 1' ELSE IF Parent Logic?.'Logic 2?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 2' ELSE 0

The list can now be set as a production list as there are no “hard references”.  Also, the formulas are smaller, simpler and now more flexible should the logic need to change.  If Parent 3 needs to use Logic 2, it is a simple change to the checkbox.

2019-04-18_11-08-24.png

 

2019-04-18_11-08-41.png

 

Appendix:

Blueprints:2019-04-18_11-16-48.png 

 

 

Comments

One of the biggest challenges I have with my clients is that they are not clear as to what define as Production List and what is structural.  I have seen some clients define all their lists as Production,  while some clients will define all their lists as strucutural, as well as everything in between.  Accordingly, many of clients have run into issues with synch errors or gaps in their model due to the lack of clarity on this subject. I have seen some documentation on definition of production vs. structural data, but it defintitely needs to be revisited and more clearly artiuclated as to what is best practice here.