Multiple SUM&LOOKUP splitting workaround

Hi,

I am trying to split multiple SUM&LOOKUP's used in a lineitem in a module in order to optimize the model performance. But while splitting the SUM & LOOKUP into different line items in separate modules (dimensioned by lists used in SUM&LOOKUP) , the result is not coming as expected. Since the size of lists is quite huge, i cannot even combine the lists being used in the SUM&LOOKUP as part of one module and then aggregate the value to final module. I know its not best practice to use multiple SUM&LOOKUP together, but the requirement is such that we have to sum the transaction value based on multiple attributes . Is there any workaround for the same where more than 3 SUM&LOOKUP are being used in a single line item

Thanks in advance!

Khushboo

Accepted answers

All comments

rob_marshall

@khush89 ,

Can you please provide a little more information? Ideally, you would create a Calc Module that just does the sum piece of the attributes needing to be summed. Then, you can do a lookup to that module in order to get the desired information. Again, if you can provide more information, maybe we can be of more help.

Rob

khush89

Thanks @rob_marshall

I have attached screenshot of SUM&LOOKUP i am trying to do using Calc modules as you have mentioned already. But this approach seems to work only if we have one attribute to do SUM&LOOKUP on. In case of multiple attributes, the result doesnt seem to look correct.

Below is how the splitting of lineitems is done in the Final module:

Value: Line item which need to be aggregated based on List 1 and List 2

Aggregate level 1: 'Sum Calc 1'. 'Aggregate 1'[LOOKUP:List1]

Final Aggregation: 'Sum Calc 2'. 'Aggregate 2'[LOOKUP:List2]

Let me know if you need more details

Calc_1_82229.PNG

Calc_2_82229.PNG

Final_Module_82229.PNG

Misbah

@khush89

See if this helps

1. Create a module (call it Module A) dimensioned by List 1 and List 2 and use below formula in Line item (call it X)

X = Final Module.Value[SUM: Final Module.List 1, SUM: Final Module. List 2]

2. In your Final Module Expected Value Line item use below formula

Expected Value = Module A.X[LOOKUP: List 1, LOOKUP: List 2]

Misbah

khush89

@Misbah

This would give me correct result, however issue in my case is the size of lists being used is really huge (~ million items in each list). And combining them in a separate module is increasing the model size by 10 GB.

Misbah

@khush89

Ah I see!! If you ask me I would always give paramount importance to the performance. End users don’t see or don’t

know the size impact but yes you will have to take a judicious call in this situation and see if consuming this amount of space is actually improving your performance. Also in Hypermodel world it becomes more important than ever to follow best practices and not worry much about space. Will let @rob_marshall speak more on this

Cheers

Misbah

rob_marshall

@khush89 ,

So, each one of those lists comprise of a million+ members, is that correct? Combining those into one module would be expensive from a space perspective. A couple of questions:

is there a relationship between the two lists? As in, is List 2 an attribute of List 1?
what did the original SUM/LOOKUP formula look like, or is that in the Final Module picture?

Thanks,

Rob

khush89

@rob_marshall

Yes correct, there are million+ items in each of these list. To answer your questions,

1. Apparently there is no relation between List 1 and List 2. However, both of these are as attributes in the Final Module.

2. Original SUM&LOOKUP was: Value[SUM:List 1,LOOKUP: List 1, SUM: List 2, LOOKUP: List 2]

rob_marshall

@khush89

Do the members in List 1 span multiple members in List 2? Meaning can 19042FAB2 have different List 2 members other than 001000033 or is 19042FAB2 always tied to 001000033?

If the answer is yes, you can create a list (not really a fan of this but it beats the 10 GB space issue) where you find the first occurrence of List 1 and List 2 concatenated together. Then, in this module, you create a line item doing a FindItem(New List, List 1_List2). In a new Calc module, you sum the data off the Finditem() member. This way, you will get the correct numbers and it will not be so crazy sparse.

Rob

khush89

@rob_marshall

Yes this approach works for me. I am concatenating items from both lists and doing SUM on line item (module based on concatenated items list) and then doing lookup on another lineitem (Final module).

Thanks!

Quick Links

Can you help?

Unanswered questions

How to extract Previous Column Ending Balance as Current Opening Balance (without time dimension)
I want to track the Opening balance, drawdown, and ending balance for the following specific dates. I created the following dates in General List. Then I created the following module to track the balance, which there is a line item called Beginning balance which is the previous value of the Ending Balance. Draw/Repayment…
Has anyone taken a stab at Master Anaplanner Recertification exam yet?
Just curious :)…
Solution for indent diabled for hierarchical list when the page has more than two axis
Hello fellow Anaplaners, I have a question about the UX for Anaplan for hierarchical list. If I have a list with multiple layers, say P1, P2, P3, and P4, and the workpage had 2 axis, list and line items, in its vertical axis and time in its horizontal axis (see figure below) The list is shown without indentations, and may…