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
Best Answer
-
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
2
Answers
-
Read it two times but I think I need a picture to get it 🙂
0 -
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???
0 -
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
0 -
@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 ?0 -
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
0 -
Take a look at this, it might give you some ideas.
Rob
0 -
@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...
0
