Simple Sum

I have 2 modules, EPOS Daily Module which has EPOS data stored with a 'cust-bvfsku' and a date, updated on a daily basis.

Then I have a top level module EPOS Sales List Module, which has a single line for each unique Cust-BVFSKU, and a start and end date.

I want to SUM the p_unit Qty from the top module into P_Sales in the bottom module where the p_Cust-BVSku is the same and the p_date falls between start date and end date in the bottom module.....

Currently i have to export the data to excel, do a simple sumifs and then re-import...... not ideal

thanks

Accepted answers

rob_marshall

@DeveloperCYT,

Can't you do something similar to what we did in this previous post? Remember, TimeSum can not go down to the day, so I am thinking the second method in the below link will be your friend.

https://community.anaplan.com/t5/Best-Practices/Using-a-date-range-in-a-SUM/m-p/38621#M3792

Hope this helps,

Rob

DavidSmith

@DeveloperCYT

Ok, this has been driving me nuts, but I think I've found a solution

1. Create a module by Products and day and bring in the values from the source transactions using a sum on the product list and the date:

'Transaction Module 2'.Value[SUM: 'Transaction Module 2'.Product, SUM: 'Transaction Module 2'.Date]

2. Cumulate the data

Cumulate the values from 1. N.B. Use PREVIOUS rather than CUMULATE as for long timescales this is much more efficient:

Value + PREVIOUS(Cumulate)

3. In the target module, bring the cumulated value from the end date and subtract the cumulated value from the start date. You need to subtract 1 from the start date to include the start date in the calculation

Product by Day.Cumulate[LOOKUP: Products, LOOKUP: End Date] - Product by Day.Cumulate[LOOKUP: Products, LOOKUP: Start Date for Sum]

Yes, the product by day module will be a big module, but it is a simple solution and you can turn the summaries off as there is no need for subtotals in the Product hierarchy or timescale

Let me know if this works for you

David

All comments

rob_marshall

@DeveloperCYT,

Can't you do something similar to what we did in this previous post? Remember, TimeSum can not go down to the day, so I am thinking the second method in the below link will be your friend.

https://community.anaplan.com/t5/Best-Practices/Using-a-date-range-in-a-SUM/m-p/38621#M3792

Hope this helps,

Rob

DeveloperCYT

Hi Rob,

With fresh eyes, i looked at your solution, and i finally got it working.

Thanks for your assistance, i now have a method, that i can use ongoing

Happy Christmas

DavidSmith

@DeveloperCYT

I might have misunderstood, but I think there is a simple solution that just needs one more line item in the source module

1. Add a line item to check if the source row date falls between the start and end dates of the top module

2. Then sum the 'Amount to Sum?' back to the summary module

I hope this helps

David

DeveloperCYT

Hi David

Thank you for your suggestion.....however i think i have a slightly different scenario...

I have 2 sources of data, both numbered lists, that are imported.

list 1 has products and a single date and a unit qty

list 2 has products and 2 dates (start and end)

I want to total the unit qty from list 1 where product matches and date is between the start and end.

Also there could be multiple periods of dates per product

P1 01/01/2018 to 31/01/2018

P1 01/03/2018 to 30/03/2018

P1 01/05/2018 to 31/05/2018

etc

It seems such a simple thing to do, and takes 10 seconds in Excel...

Your solution uses modules that are not from numbered lists, and i cannot quite get my head round

how i convert the numbered lists to become modules such are yours?

DeveloperCYT

The issue is the same product, with multiple date ranges, which means i cannot have a module based on the product, and that is where this problem becomes unsolveable.

I will continue to export to Excel and back, but thanks for the help, and this is a useful piece of information i will store away.

DavidSmith

@DeveloperCYT

Ok, this has been driving me nuts, but I think I've found a solution

1. Create a module by Products and day and bring in the values from the source transactions using a sum on the product list and the date:

'Transaction Module 2'.Value[SUM: 'Transaction Module 2'.Product, SUM: 'Transaction Module 2'.Date]

2. Cumulate the data

Cumulate the values from 1. N.B. Use PREVIOUS rather than CUMULATE as for long timescales this is much more efficient:

Value + PREVIOUS(Cumulate)

Product by Day.Cumulate[LOOKUP: Products, LOOKUP: End Date] - Product by Day.Cumulate[LOOKUP: Products, LOOKUP: Start Date for Sum]

Yes, the product by day module will be a big module, but it is a simple solution and you can turn the summaries off as there is no need for subtotals in the Product hierarchy or timescale

Let me know if this works for you

David

DeveloperCYT

Brilliant, and such a 'simple' solution. (eventually)

The figures in 'Total Promo Epos' is your solution, the figure to right is Export to XL, calc and Import... identical.

Thank you so much for your continued effort to help me with this soilution, it is very much appreciated, and has made a big difference to my modelling.

Thanks

Kev

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
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…