I wanted to get a subtotal of product1 based on the selected GRP2 & GRP9 product groups in the target module. I tried to use LOOKUP, SUM, and SELECT, I received an error. Please advise me on this case.
@andis how you presented the data, they are not "normalized".
For the same Product1 .. you could have different combinations of GR1 and GR9. the SUM would work only if you have GR1 and GR9 as "properties" of the Product list ( line items GR1 and GR9 in a system module for Product list).
However... you could create another intermediary module with the lists Product, GR1 and GR9 lists and use "firstnonblank" aggregation from the module 2 and create 2 line-items for GR1 and GR9.. .and based on the results you can create some numeric Flag ( 0 or 1) at the intersections between Products, GR1 and GR9 where both line-items "firsnonblank" are not blank.
However, the data would result duplicated for the same Product1 if it will be found 2 or more combinations for the same product of GR1 and GR9 elements.
in "module target" you can just multiply the data from Module 1 and the intermediary module...
Hi Alex, FIRSTNONBLANK function is to skip empty cells and then take the next cell. In my case, the source module 2 always has a certain code because Group 1..Group 9 are lists. If you don't mind, I want to listen to Anaplan best practices for my business case above.