Help with SUM

Hi Guys,

I have a target module containing a dimension which I need to map into a line item. Taking the system module containing the source dimension I have created a line item subset containing the line item in the target and mapped the target line item to the source dimension.

In addition to this I have a line item subset as a dimension in the source which I am mapping into a line item subset which is a dimension of the target. I am using a system module dimension as the source LISS and mapping this into the target LISS.

When I build the formula using two SUM functions no data is pulling through to the target. I do not receive any error and the formula simply returns zero.

When I replace the first SUM with a SELECT the data successfully flows through to my target module.

I am baffled as to why the first SUM is failing but not generating any error. I need to SUM as two list items in the source need to aggregate into a single line item in the target.

Any and all ideas as to why this is happening would be happily received.

Thanks,

Chris

Accepted answers

Misbah

@ChrisAHeathcote

Are you saying both the SUM's don't work?. I see this as a typical use case of SUM & SELECT - reason being

In Source Module you have 5 dimensions

L4
Archive Period
Future Year
Archive
LISS OUT01

In Target Module you have 4 dimensions

L4
Archive Period
Future Year
LISS ARC

Between these two modules 3 dimensions are common whereas 1 differ and another one is missing in the target module. The one which differs is LISS. In order to tackle this you already have a mapping module dimensioned by LISS OUT01 which has an attribute of LISS ARC and you are using SUM on that line item to pull the values- perfect

On the other hand the missing dimension in the target module (Archive list) can't be summed upon. However Anaplan will bring the totals automatically if it has top level set. Alternatively it can also be tackled with SELECT if you wish to pull the value of any specific list item.

This is what I believe is happening. Please tell me that I am right in assuming everything here:)

Misbah

All comments

nathan_rudman

Read it two times but I think I need a picture to get it 🙂

ChrisAHeathcote

@nathan_rudman

Here is a walkthrough of what I am trying to achieve.

My source module;

Contains the following dimensions;

Time - Months (Future Years)
L4
Archive Period
LISS : OUT01

My target module;

Contains the following dimensions;

Time - Months (Future Years)
L4
Archive Periods
LISS : ARC Report Line Items

I have two mapping modules dimensioned by LISS : OUT01 which SUMs the source LISS into the target LISS and another dimensioned by Report Header ( the parent list of the Archive subset ) which maps the report headers in the source to the line items in the target.

When I use SUM for both mappings no data flows through and I receive no error. When I change the second SUM to a SELECT the correct data flows through and the report header ( Archive ) maps through to the correct line item in the target.

I can use SELECT to pull through all the relevant data and map this to a single line item.

However, WHY WILL THE SUM NOT WORK???

rob_marshall

@ChrisAHeathcote

Chris, it is hard for me see actually what is going on, but if it works for a select but not a sum, you might want to consider creating a mapping module for the LISS. So, the target LISS, create a mapping module with a line item formatted as the other LISS, and then sum off that mapping module. See if that helps.

Rob

Misbah

@ChrisAHeathcote

Are you saying both the SUM's don't work?. I see this as a typical use case of SUM & SELECT - reason being

In Source Module you have 5 dimensions

L4
Archive Period
Future Year
Archive
LISS OUT01

In Target Module you have 4 dimensions

L4
Archive Period
Future Year
LISS ARC

This is what I believe is happening. Please tell me that I am right in assuming everything here:)

Misbah

alexpavel

@ChrisAHeathcote what you try to achieve should work.

The only particularity I see in the line-item subsets in these cases is that they do not return an error of not having a total root element.
So, if the mapping of the SUM: is not done correctly it is returned zero value, without any error warning. This could the reason why you do not receive any error, but also wrong results.

The only difference that I see between the source and target modules are the subsets lists "LISS : OUT01" and "LISS : ARC Report Line Items"

Can you share also the dimensionality of the "PR20 LISS: OUT01" module ? is it based on "LISS : OUT01" and the line-item 'LISS: ARCxx REPORT LINE ITEMS LIST" is formatted as "LISS : ARC Report Line Items" ?

Normally you would not need another SUM: other than for "LISS : ARC Report Line Items". to what list is related the "PROP15 REPORT HEADER.'Archive report' line-item ?
What is the dimensionality of "PROP15 REPORT HEADER" module ?

ChrisAHeathcote

@Misbah

Yes, I believe you are correct and in this instance I went with SELECT().

But my main issue here is trying to understand why my SUM mapping did not work.

I was attempting to map Archive List ( subset of Report Header ) in the source to a line items in the target.

Using a system module for the Archive List I created a list formatted line item dimensioned by my target LISS.

This informed my SUM with is the Archive Report line item in the original second SUM.

I expected the data to flow but I received no error.

When I changed the SUM to SELECT() data pulled through successfully.

Just looking to understand why the first solution did not work. Is the fact that I am trying to map into line items the reason the SUM failed?

Chris

rob_marshall

@ChrisAHeathcote

Take a look at this, it might give you some ideas.

https://community.anaplan.com/t5/Best-Practices/Variance-Analysis-With-Native-Versions-Made-Easy/tac-p/98381#M708

Rob

alexpavel

@Misbah Nice catch!

I did not notice the 5th List in the source module... "Archive".

The assumption is that in the target module @ChrisAHeathcote wants to extract in the Target module only 1 Archive element at the time...

A solution to solve this with SUM is to use the values from the system module between LISS OUT01 and LISS ARC to show the "LISS ARC" values only in correspondence of the Archive list desired to be extracted.

The steps are:

1. have in a system module without list the line-item formatted as "Archive" and choose the value of the Archive list that needs to be extracted in Target module

2. add a new line-item in the source module formatted as LISS ARC and with apply to only "LISS OUT01" and "Archive"

3. formula: if item(Archive)) = system module.Archive then mapping module.LISS ARC else blank

4. in Target module use in the SUM the line-item from source module ( point 3).

this is another way to solve SUM and SELECT.... 🙂

hope it helps...

Quick Links

Can you help?

Unanswered questions

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…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…