calculation

andis
andis
in Modeling

Hi experts,
I have three modules below:

andis_0-1614088166619.png

 

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.

Regards,

 

Tagged:

Comments

  • alexpavel
    alexpavel

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

     

    Hope it helps...

    Alex

     

  • andis
    andis
    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.

    Regards,
  • alexpavel
    alexpavel

    @andis  this is what I understood from your request...

     

    Module 1:

    alexpavel_0-1614161401911.png

     

    Module 2:

    alexpavel_1-1614161443244.png

     

     

    and the results in Module Target to be like this:

    alexpavel_2-1614161487499.png

     

     

    In order to make this work... you need an Intermediary Module:

    alexpavel_3-1614161541495.png

     

    Hope it helps...

    Alex

     

     

     

  • andis
    andis

    Hi Alex,

    I appreciate your information. I will try your suggestion later. Meanwhile, I've changed my configurations:

    1. Added New list (numbered list type) with some properties. The content is the same as Source Module 2 above. 

    2. Put the SUM function in the target module.

     

    andis_0-1614174963898.png

    My SUM function: Source module 1.Amounts [SUM: Product>Groups#.Group 2, SUM: Product>Groups#.Group 9]

     

    Error message: The dimension of mapping used for aggregation doesn't match any dimension of the source.

     

    Questions:

    1. Could you see something wrong?

    2. I've created the new list above to replace Source Module 2. Which one is a good solution (PLANS)?

    3. You may give other thought.

     

    Regards,

     

    Andi

     

  • alexpavel
    alexpavel

    @andis  is how the SUM function works in Anaplan.

     

    You need to compare the granularity of your Source data and the granularity from where you define the properties to summarize the data.

     

    in your case: Amount granularity is the combination of: Product, Version, Time, Accounts

    The properties Group 2 and Group 9 are defined at Product>Groups# level.

     

    In order to make work this: Source module 1.Amounts [SUM: Product>Groups#.Group 2, SUM: Product>Groups#.Group 9] ... 

    You should have the granularity of the Amount at: Product>Groups#, Version, Time, Accounts

     

    Anaplan cannot summarize Amount data with the granularity of Product on a property that is defined at the granularity of Product>Groups#

     

    Hope it helps...

     

Categories