Sum of amount based on numbered list.

Highlighted
New Contributor

Sum of amount based on numbered list.

 

 

I have a numbered list, which has the same names (display names). These same name line items then have different child.

The values of the same name list items needs to be added up to only one list item which has the latest PO date in the same list.

 

For Example-

 

This is the source module that has the same parent names with different child names in it. Also the values of the same PO’s are differentiated in terms of the PO Creation Date.

 

subhashree_0-1580884934377.png

 

 

The destination module is:-

subhashree_1-1580884934380.png

 

Here the values needs to be added up. The values that are shown here are wrong. For the PO 53852328_1 which has a similar name but the values are different; hence the value for 53852328_1 should be 20+40= 60 for one PO and the 2nd should remain the same value depending on the PO Creation Date(here 1st one should remain same and the 2nd repeated PO should have the value of 60). I am trying to figure out how to get the solution. Any help or suggestions would be appreciated.

 

Thanks

 

7 REPLIES 7
Certified Master Anaplanner

Re: Sum of amount based on numbered list.

hi @subhashree 

My suggestion is the following:

1. create a new list (no numbered) 

2. Make sure that all the unique parents of your source list are in the new list. For instance, you can create an import action that loads the field "PO_Num_Line" as List Item names in the new list.

AlejandroGomez_0-1580893523718.png

 

3. The action above will reject all the duplicates and you will end up with a list with all the unique names of your "parent items"

4. Again, in your source list, create a new Property formatted as the new List you have created at step 1.

5. Enter the following formula: FINDITEM("New List","PO_NUM_LINE"). This should retrieve the items from the List you have created at step 1.

6. Finally, in your target module, in the "Value" line item, enter the function "[SUM: new property from the source list]".

 

These steps should allow you get the numbers you want without major changes in the current structure of your model.

Please, let me know how it went.

Cheers,

Alex

Olivehorse

Highlighted
New Contributor

Re: Sum of amount based on numbered list.

For the above case, I have a doubt

 

The 1st PO - 5385328_1 has the amount = 10, the 2nd PO - 5385328_1 has the amount = 30.

1st PO creation date is old & we are not worried about that, the 2nd PO creation date is new and I want to add the Amount from 1st PO to 2nd PO. So at the end 2nd PO Amount = 40. (The correction with respect to the screenshot).

Highlighted
Certified Master Anaplanner

Re: Sum of amount based on numbered list.

hi @Akshaykumar 

 

1. Create a list with the unique POs (so you will only have one item for "53852328_1")

2. Create a module with the list of unique POs and a Line Item called "latest date"

3. Enter the following formula:  Source.Date[MAX: Source.Mapping to Unique POs]. 

 

The above description together with the previous answer will provide you a list with all the unique PO codes, the latest date for each PO and the total amount per PO.

 

Check out an example:

Source: all POs with their creation Date and a LI with their "Unique PO" from the list created at 1

AlejandroGomez_0-1580903383426.png

 

Target (notice that 53852328_1 is showing the latest date)

AlejandroGomez_1-1580903425778.png

 

I hope that solves your question. 

 

Regards,

 

Alex. 

Olivehorse

 

PS: may i ask whether you @Akshaykumar  are working together with @subhashree  on this?

 

Highlighted
New Contributor

Re: Sum of amount based on numbered list.

@AlejandroGomez  yes we both work together.

Highlighted
Certified Master Anaplanner

Re: Sum of amount based on numbered list.

Nice. And did my second post help you out @Akshaykumar  @subhashree  ? 

Highlighted
New Contributor

Re: Sum of amount based on numbered list.

@AlejandroGomez 

The solution provided by you was for a flat list, and what we have is a hierarchical list; as mentioned below.

subhashree_0-1580969667413.png

 

 

Here, this is the source module which has the dimensionality as mentioned with the first 2 columns representing the hierarchy.

 

subhashree_1-1580969667416.png

 

This is the target module in which the first 2 columns represent the hierarchy. The 1st column is another hierarchy that roll up to P2 level.

 

subhashree_2-1580969667417.png

 

This is the expected data in the target module.

 

 

Highlighted
Certified Master Anaplanner

Re: Sum of amount based on numbered list.

Hi @subhashree 

 

I noticed that your source module was using a composite hierarchy, and Indeed my example uses a flat list. I believe it does not change neither the nature of the issue nor the solution and I assumed it will serve the purpose of illustrating a solution. Sorry for not making it clear enough. 

 

I believe we are totally on the same page, just bear in mind that in your source module, you can aggregate all the invoice numbers (level 2 of your hierarchy) to PO NUM (level 1 of the hier.) just by leveraging Anaplan's automatic aggregations across hierarchy's items. This will give you totals of Invoice amounts at PO level, right? 

 

Then, at target module, by using the new list with only unique PO NUM items (As explained in my previous post, together with FINDITEM and SUM) you can sum up all the amounts of the same PO NUM item in just one unique item (eg: make the item "532838_1" display the aggregation of all the items "532838_1" from the numbered lists = 89,000 + 83,655). I am sure that just by following the steps you will get to obtain the results you are seeking. 

 

Give it a go and let me know, please.

 

Cheers.

Alex.

Olivehorse