Simple Sum

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.


EPOSa.png

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.

 

Eposb.png

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

 

 

Best Answers

  • @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

    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]

     

    2018-12-20_15-48-46.png

     

    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

     

     

Answers

  • 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

     

  • @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

    2018-12-12_10-17-10.png

     

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

    2018-12-12_10-17-37.png

     

    I hope this helps

    David

     

  • 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?

     //

  • 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.

     

     

  • 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.
    compare.PNG

    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