calculation

Hi experts,
I have three modules below:

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,

Accepted answers

All comments

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

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

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

Module 1:

Module 2:

and the results in Module Target to be like this:

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

Hope it helps...

Alex

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.

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

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

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…