How to Resolve “Automatic Sum of Hierarchy Not Possible” Error Without a Top-Level Item?

StellaO

Hi Anaplan Community,

I’m encountering an error in my model: “Automatic sum over Over/Under hierarchy is not possible because the list does not have a built-in top level.”

I understand that one solution is to set up a top-level item in the list. However, I prefer not to use a top-level item. I’ve read that using a LOOKUP on a constant or mapping module can also resolve this issue, but I’m not sure how to implement this.

Could someone provide detailed steps or examples on how to use a LOOKUP on a constant or mapping module to bypass the need for a top-level item? Any guidance or best practices would be greatly appreciated!

Thanks in advance for your help!

AjayM

Hi @S.O ,

Here are the steps you can follow to achieve aggregation upon a list that doesn't have top-level item or parent level.

1. Create a non-production list called 'SUM Helper List' and add a list item calling it as 'SUM Helper'
2. In a sys module, like "SYS Lookups", add a lineitem of no dimensions, but of 'SUM Helper List' format, and just pull the list item (Formula: sum helper list.sum helper)
3. In the 'SYS over/under' module, add a lineitem called 'Over/Under SUM Helper' of format 'SUM Helper List', and just pull the list item (Formula: sum helper list.sum helper)
4. At this stage, you have 'sum helper' coming against every list item of 'over/under' list, in this SYS module
5. In the 'O2 Burn Rate' module, add a new lineitem 'Staging Lookup: Over/Under Selection', and add 'sum helper list' as additional dimension to the original dimensions of 'Lookup: Over/Under Selection' lineitem.
6. Formula in this staging lineitem should include [SUM: 'Sys over/under'.'Over/Under SUM helper'], in addition to the original formula
7. In the original 'Lookup: Over/Under Selection' lineitem, refer to 'Staging Lookup: Over/Under Selection' lineitem, using lookup on the lineitem you added in step #2.

To avoid sum and lookup within same lineitem (which is not a recommended practice to have them both), we have introduced the staging lineitem.
You can use this 'sum helper list' with any list that has no parent or top-level, and needs aggregation.

Cheers!

StellaO

@AjayM - thanks for the response.

Please can you elaborate on 5 through 7.

For #5 - so the dimensions for the staging line item should match those in the 'Lookup: Over/Under Selection' line item PLUS the 'Sum Helper List'?

For #6 - What should be the complete formula for the staging line item? And what is the format for this line item?

For #7 - How should it be referenced?

Thanks!

luke_e

I might be missing something here, but sounds like all you need is a mapping module with one line.

1. That one line should have a format of your 'list' that represents the highest level of that hierarchy (whether composite or not). e.g. Total Company/Total Product etc.
   1. It also doesn't need to be dimensionalised by anything; it's just a single cell.
2. Then, in that new line, select that highest level item in the list.
3. Go back to the line thats giving you the error, then add [LOOKUP: Mapping Module.Line Item] and it should be done.
4. If you need to push this change to a production model, please note you'd need to manually redo step (2).

The only times this might not be the case is if,

• you don't have a true total in a list, in which case you may need to create one as part of your composite list
• you're doing SUM and LOOKUP, then you might want to stage the calculation, but if it's just a simple summary, the above should suffice.

AjayM

@S.O ,
#5 - your understanding is correct
#6 - Format should be same as that of original lineitem (must be number here). Staging lineitem formula: 'U04 Over/Under Selector'.User Selection[SUM: 'Sys over/under'.'Over/Under SUM helper']
#7 - Formula for 'Lookup: Over/Under Selection' : 'Staging Lookup: Over/Under Selection'[lookup: SYS Lookups.sum helper]

If still not resolved, please share snapshots in blueprint view of your changes.

Cheers!