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
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.
Hope this helps,
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
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
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
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
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?
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.0
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.