Line Item Subset Summary Level, workaround needed please!

dsoomar002
Certified Master Anaplanner

Re: Line Item Subset Summary Level, workaround needed please!

@rob_marshall - Hmm... no I am not using collect, and don't want to use collect for this setup. 

 

In the video LiSS Creation is a constants module, with only Line Items. This is the module I point the LiSS to when creating it.

 

Module B is a Data Load module (List 1 x LISS Numbers x Versions x Time x Line Item (amount)

 

This is where I am loading data, not in the LiSS Creation module. 

 

A collect statement would pull the numbers from LiSS Creation not Module B. 

 

In the Summary Report, I want to pull the final number from Module B. 

 

You may be wondering why create a LiSS in a Constants Module, the value add, is both the Data Load module and other override modules only have 1 line item instead of 4. In this oversimplified example 1 vs. 4 is not that big of a difference. But in my real world example I have 100, 200, or 300 numbers being added together.

 

So in essence I am reducing hundreds of line items down to 1 which is both easier to audit and maintain.

AntonMineev
Regular Contributor

Re: Line Item Subset Summary Level, workaround needed please!

More complex workaround.....I have it, yes 🙂

 

You need LOOKUP with not-list elements. And I do this with SUM and TECH LISS:

 

Снимок экрана 2021-04-23 в 23.09.43.png

Let's go:

1. Make tech module with formulas: ISANCESTOR(Choose.Choose LI, ITEM(LISS)) and (WARNING) LI MAP = IF ISANCESTER? THEN AMOUNT.Amount ELSE BLANK. 

What is amount? It is a tech LISS with LI from your target module.

2. USE SUM FUNCTION in the target module: Source.Amount[SUM: TECH LISS Choose.MAP]

 

You can see all in my screenshot.

The basis of this chip is that we can use SUM together with LI that we summarize.

P.S. If it helps you, give me Master Anaplanner 🙂

dsoomar002
Certified Master Anaplanner

Re: Line Item Subset Summary Level, workaround needed please!

Thanks for the insight. So unfortunately this solution was not able to work for me either @AntonMineev. The result still returns 0 when I do a SUM after mapping the Numbers to Amount. 

 

However, I actually found a solution thanks to matthew.mccurdy@slalom.com - I just changed my LOOKUP to SELECT and pointed it directly to Number 4. 

 

Since LiSS are not a production list and don't impact ALM...I think this may be an instance where we can make this exception! 

 

@rob_marshall - question from a Planual/best practice standpoint-

 

1) Is this limitation, a limitation of the lookup, or the line item subset or both? Where we can't perform a lookup against a number that has "Is Summary" checked?

 

2) If the rule is we can't do a LOOKUP then why does SELECT work in this instance? I thought using a Constants module with a LOOKUP is an equvilent alternative to creating a SELECT?

View solution in original post

AntonMineev
Regular Contributor

Re: Line Item Subset Summary Level, workaround needed please!

@dsoomar002 

I'm surprised my formula didn't work. I specifically reproduced in my workspace and reviewed your video 🙂
Did the LI MAP work correctly? Is its format the same LI on which the SUM formula is written? 

In my example it didn’t work only then, the spell you have in the last module also has the same reference as in the example (in our example, LISS with number 4). I reviewed the video and there is no such thing.


About SELECT/LOOKUP.

We have two general problem for LOOKUP:

1. LOOKUP doesn’t work with summary “Formula” (SELECT works).

2. LOOKUP is not designed to work with the aggregate items on non-composite lists (lists with no parent hierarchy defined). (Copy from Anapedia).


You can try SELECT, but since you can have hundreds of LIs, in theory you may need to choose a different non-list element instead of number 4. this story will not be flexible (PLANS).

 

AntonMineev
Regular Contributor

Re: Line Item Subset Summary Level, workaround needed please!

Hi @dsoomar002!

LOOKUP formula doesn't work with summary elements.

You can make some option:

1. Change the formula in module A. Add + 0.000000001 there. And it won't be cumulative (or +0, but I don't remember if it works).

2. Remove the "Is summary" checkbox from LI Number 4 in Module A. Then it will not be a node in the subset.

3. More complex workarounds.