Production Lists and Ragged Hierarchies Logic
Assume the following Non-Composite list, ragged hierarchy, needs to be set to Production Data.
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.
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.
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.
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.
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.
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.
Then you can refer to the logic above in the calculations.
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.
Appendix:
Blueprints
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.
1 -
@mjpearlman definitely agree the concept of a list being set to "production data" is key for the client to understand. I like to explain it this way:
Production Data:
Lists whose members will be added to by end users
Lists whose members will be frequently updated (Adds/Deletes/Re-orgs)
Very large lists (may want to consider production data to allow for the DEV model to be a subset of the total list) --> if size is a major constraint
Maintenance? (No) --> Production model list is updated and NO SYNC is required!
Non-Production Data:
Lists that are infrequently updated, example - pick lists, or currency codes
Maintenance (Yes) --> Development model needs to updated first and a sync is required from DEV to PROD!
I tend to push for almost all lists to be set as "production data", and then prove why it should be moved to "non-production".
Thanks,
Brett Francis
2 -
I have a similar challenge to use Rank at different levels in the hierarchy. In the example the total % is 300% how can we obtain the correct % at the parent level so that the total is 100% ?
0
