group by line item

Hi,

I have two different modules using two different dimensions.

module 1

module 2

In module 2 I have an amount value, which I want to pull in to module 1.

similarity between two module is Line item A (repeating text value), while pulling in to module 1 ,I want to group in amount value by Line item A( common line item in both the module).

Accepted answers

bram.kurstjens

Hi AshaPatil,

You probably want to create a list (lets call it "List A") based on the text line item A and change the dimension of module 1 to List A. Then change the format of line item A to List A.

In module 1 you can do:

'Module 2'.Value[SUM: 'Module 2'.Line Item A]

Edit: If you do not want to change to dimension of module 1, then you could also change the line item of module 1 line item A to format List A. The formula would then become:

'Module 2'.Value[LOOKUP: 'Module 1'.Line item A, SUM: 'Module 2'.Line Item A]

Hope this helps,

Bram Kurstjens

All comments

bram.kurstjens

Hi AshaPatil,

You probably want to create a list (lets call it "List A") based on the text line item A and change the dimension of module 1 to List A. Then change the format of line item A to List A.

In module 1 you can do:

'Module 2'.Value[SUM: 'Module 2'.Line Item A]

Edit: If you do not want to change to dimension of module 1, then you could also change the line item of module 1 line item A to format List A. The formula would then become:

'Module 2'.Value[LOOKUP: 'Module 1'.Line item A, SUM: 'Module 2'.Line Item A]

Hope this helps,

Bram Kurstjens

Misbah

@AshaPatil

When we have to aggregate the data we use SUM function with caveats that

1. SUM to be applied on List formatted line item or list property

2. That Particular list needs to be dimension of the target module - where you want to pull the data in.

Or Else there is another way of doing it if you just want to aggregate it in same module you have to add LOOKUP function along with SUM Function

Formula be like : Amount[SUM: List Formatted Line item, LOOKUP: List Formatted Line item]

Please be aware that there are certain performance issues with SUM & LOOKUP being used together - there is an workaround that Anaplan has suggested - REFER SUM&LOOKUP Function in Anapedia

Happy Planning!

Thanks,

Miz

rob_marshall

@AshaPatil ,

Please don't use Sum and Lookup together in the same expression as this could lead to performance issues (Planual rule 2.02-08), break them up into different line items/modules. From what I can tell from the above (pictures would be nice to know exactly what you are trying to achieve), you will need to convert the text string into a list formatted line item and then you can do a sum off of that line item as Bram mentions in the first example.

Hope this helps,

Rob

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…