Line Item Subset Summary Level, workaround needed please!

DaanishSoomar

Hello,

 

I have run into an issue for which I could not find any documented limitation or bug, but I think I may have hit one. I am looking for a solution to the following scenario:

 

Let's say I have a constants module, ModuleA, that I am driving a LISS off of. 

 

Number1

Number2

Number3

Number4 = (Number1+Number2+Number3)

 

Let's create a LISS from the 4 numbers above. We can call it LISS Numbers.

 

I have a module, Module B, where I can load in data into each line item, (List 1 (top level: All List Members) x LISS Numbers x Line Item (amount) x Versions x Months). 

 

Top level: All List Members

Actuals:

Number1= 10

Number2= 10

Number3=15

Number4=35 (10+10+15) 

 

Now I want to pull Number 4 into Module C, which is: Versions x Months x Line Items (Number4summary)

 

Number4summary = ModuleB.Amount[LOOKUP: SYS00 LISS Lookup Module.Number4]

 

SYS00 LISS Lookup Module.Number4 is a System Constants Module with a List Formatted Line Item (LISS Numbers Format) that is selected as Number 4.

 

The problem I am running into is the model is not interpreting this at all. So I am returning 0. The funny thing/interesting thing is, if I change the dropdown from Number 4 to Number 3, I am able to return 15 or when I choose Number 2, I am able to return 10 successfully. The LOOKUP fails only when I select Number 4 in the constants module dropdown.

 

Does anyone have any workarounds? Is this a limitation of a LISS? That you cannot lookup summary line items? This should be pretty easy to replicate in a playground environment. 

 

Now I could do something like:

 

Number4summary= ModuleB.Amount[LOOKUP: SYS00 LISS Lookup Module.Number1] + ModuleB.Amount[LOOKUP: SYS00 LISS Lookup Module.Number2] + ModuleB.Amount[LOOKUP: SYS00 LISS Lookup Module.Number3]

 

However, again, I would much rather just pull Number 4 and just refernce the total once, rather than recalculating the total. My real scenario has about 65 numbers on the actual scenario so doing that kind of aggregation manually would be painful, but would prefer not to. And there are also a few more intermediary modules where some additional math or overrides can get applied to the LISS, but this is the oversimplified example.

 

Any help here would be appreciated, looking for a response on a fairly quick timeline for a client.

DaanishSoomar

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?

DaanishSoomar

Tagging @JaredDolich @Misbah @rob_marshall in case if you have any suggestions. 

rob_marshall

@DaanishSoomar 

 

Is the IsSummary checkbox turned on (True) in the bluepirnt where the line items are originally created?  If so, uncheck that.

 

 

Rob

Misbah

@DaanishSoomar 

 

Just turn the summary option off (by disabling then Boolean in Blue print view of that module) for Number 4 line item

 

 

.That should fix it

 

Misbah

rob_marshall

@Misbah 

 

HA!!!!  You are too late!

AntonMineev

Hi @DaanishSoomar!

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.

Misbah

@rob_marshall 

 

Late, because I was creating Screenshots for @DaanishSoomar .You have everything ready.

DaanishSoomar

@Misbah @rob_marshall - you both were mighty fast...but i'll give you guys another chance 🙂 

 

Unfortunately, turning off the checkbox does not get me the result I need. I think @AntonMineev may be onto something. See screenshots attached of my full setup...I am not getting the result I need with the summary checkbox turned on or off. 

 

I also tried adding + 0 or +.000001 and that did not help either. I may need some of those more complex workarounds 🙂 

 

Again the goal is to see 35 in the Summary report.

 

With checkboxWith checkboxWith Checkbox, number 4With Checkbox, number 3Number 4 selected, does not return anythingNumber 3 selected, works just fine with or without checkboxNo checkboxNo checkboxNo checkbox

rob_marshall

@DaanishSoomar 

 

I guess I am not understanding because the Lookup works fine for me:

 

 

 

 

DaanishSoomar

Hey @rob_marshall @Misbah @AntonMineev,

 

I just recorded a video capturing the situation, it's about 2 mins. Hope this helps explain the issue clearer. See attached. 

 

 

rob_marshall

@DaanishSoomar 

 

You missed the collect piece, I believe.  Check this out...

 

DaanishSoomar

@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

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

 

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

 

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 🙂

AntonMineev

@DaanishSoomar 

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).