Simple Sum

DeveloperCYT

Hi

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

 

 

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

 

 

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.

 

 

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