My SUM formula is only bringing back data for 2 list items but not the other 300 odd lines.
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?
Answers
-
@FionaC - Can you share screenshots of the source, destination & mapping module?
0 -
Source - CAL13
Mapping - SYS34
Destination CAL18 -
List - It is bringing through values against Misc Sales & P&P only. Not the others
0 -
@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?
0 -
I have tried adding it all at A6 level but it just returns zero's for all the list items
0 -
@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?0 -
This is the destination module
0 -
@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! :)0 -
Thanks for this, what's the formula in the Mapping doc please?
0 -
@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]0
