My SUM formula is only bringing back data for 2 list items but not the other 300 odd lines.

FionaC · 2025-03-17T10:07:06+00:00

There was an error rendering this rich post.

I have 2 modules set up, one with source data by line item that contains a 'Fund' code (CAL13) and then one with list mapping data by fund code (SYS34). I have set up a 3rd module and added in this formula

'CAL13 Consolidation by Fund'.Actual by Fund[SUM: 'SYS34 F4 Fund Details'.Account Mapping]

They both have a level of the Nominal account hierarchy (Level 5 and Level 6), however only 2 of the nominal account codes (List items) are bringing any value back, the other 300 odd lines are not returning any value. The list and subsets are set up the same.

Can anyone help with why this is happening?

Accepted answers

All comments

devrathahuja

@FionaC - Can you share screenshots of the source, destination & mapping module?

FionaC

Source - CAL13

Mapping - SYS34

Destination CAL18 -

List - It is bringing through values against Misc Sales & P&P only. Not the others

devrathahuja

@FionaC - Since you have the same list different levels, it is auto summing up the values. In your mapping line item, could you add also the A6 dimension & check the outcome?

FionaC

I have tried adding it all at A6 level but it just returns zero's for all the list items

devrathahuja

@FionaC - When you drilldown is it showing you the mapping? If the "SUM: XYZ" does not show up i.e the XYZ member mapping, it means there's some issue with the sum.

If it does show up but the value is 0, you can drill down further to understand.

Can you share a snapshot of the drilldown screen?

FionaC

This is the destination module

devrathahuja

@FionaC - Tried to replicate your use case & it works with adding the A6 to the mapping as suggested earlier-

Source-
Dimensions - Fund, A6, Time & Values

Mapping-
Dimensions Fund - Mapping for A5 (Original mapping)
A5 mapping line item with A6 dimension applied as well

Target-
Fund, A5, Time & line item

It is because A6 & A5 are in the same hierarchy that while summing up it needs that additional dimension in the mapping module to override the natural hierarchical sum.

Hope this helps! :)

FionaC

Thanks for this, what's the formula in the Mapping doc please?

devrathahuja

@FionaC - Do you mean the A5 mapping? It refers to the original mapping just has the A6 as additional dimension applied.

The final line in target has a formula of source{SUM: A5 Mapping]

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled