Using a date range in a SUM

Highlighted
Regular Contributor

Using a date range in a SUM

Hi,

 

Module1 = dailysales           has customer-product and date line items

Module2 = periodsales        has customer-product and startdate and enddate lineitems

 

I want to SUM the sales from Module1 in Module 2 where the product matches and the date is within the start and end dates

 

I can SUM using the customer-product :

periodsales.salesvolume = dailysales.volume[LOOKUP:'customer-product',SUM: dailysales.'customer-product']

but obviously this gives me the total sales for the customer-product regardless of date

 

I need to SUM If the cusotmer product matches and the date>=startdate and date<=enddate

 

something like this

periodsales.salesvolume = dailysales.volume[LOOKUP:'customer-product',SUM: dailysales.'customer-product', SUM:dailysales.date >=startdate, SUM:dailysales.date<=enddate]

 

this does not work, is there a way to do this please?

 

thanks

 

 

 

10 REPLIES 10
Highlighted
Certified Master Anaplanner

Re: Using a date range in a SUM

Hi DeveloperCYT,

 

One way I've found success with problems like this is by utilizing the concept of a "windowed" filter.  If you add another line item in Module1 called salesvolume_InWindow with a formula like: 

 

IF SalesDate > Module2.startdate AND SalesDate < Module2.enddate THEN Sales ELSE 0

(you may need to add provisions there if startdate or enddate are not specified in Module2 by the way),

 

..then you may be able to use the formula you've already used, just pointing to the salesvolume_InWindow line item instead.

 

--------

The other way to solve this problem is by introducing a third module that's dimensionalized by product-customer, a day-level Time list, and line items.  You can then utilize the TIMESUM function in this case to get the range of sales or volume for each product-customer.

 

Hope this helps,

Zaf

Highlighted
Master Anaplanner/Community Boss

Re: Using a date range in a SUM

@DeveloperCYT

Could you post some more mages or details of the modules, line items and blueprint?  It will help understand the source and target better

One thing to say, is please try and avoid using Combinations of SUM and LOOKUP in the same expression.  This can lead to poor performance at scale

David

Highlighted
Regular Contributor

Re: Using a date range in a SUM

Module with daily salesModule with daily salesModule with sale promotion periodsModule with sale promotion periods


Top module is my daily sales data, with sales by Customer - Bvfsku for each day

Bottom module is my plan of promotion periods with a start and end date, and i want to add the sales from the top module for the dates within the two SALES OUT dates.

 

calc i have in 2nd Module currently, which does it at customer - bvfsku level.

 

XL EPOS.EPOS Sales[LOOKUP: 'Customer - BVFSKu', SUM: XL EPOS.'Cust-BVFSku', LOOKUP: Promotion Periods, SUM: XL EPOS.Promo Period]

 

Sort of thing you would do in Excel

=SUMIFS(Unit qty, Customer - Bvfsku,Cust - Bvfsku,Date,>=SALES OUT start,Date,<=SALES OUT End)

 

I currenly export the data to Excel, do the above calc and then re -import....

 

Thanks

 

 

Highlighted
Master Anaplanner/Community Boss

Re: Using a date range in a SUM

@DeveloperCYT - i'd personally go with the TIMESUM formula opportunity suggested by @zafkamar. I use it a lot of the time within our models and it's very powerful, plus you can then do daily trend graphs as well!

Andrew

Highlighted
Regular Contributor

Re: Using a date range in a SUM

The TIMESUM option seems to make sense, however i a struggling to put it into practice, as its not something i have ever used.

I have a new Module with Cust - Product and Time (day) with volumes in it

(test data entered only for whole of 2015)

dailyepos.PNG

I have my Module with Cust - Product and Start and End dates, with a line item for volume.

promoperiod.PNG

 i tried this TIMESUM(EPOS Daily Module.UNit Qty, Start Date, End Date) but without result.

Some clarity on TIMESUM would help, as the Anapedia isnt very clear.

thanks in advance

 

Highlighted
Master Anaplanner/Community Boss

Re: Using a date range in a SUM

Hi @DeveloperCYT

 

In your time module would there be values outside of the promotion dates? If yes maybe the option of a 2nd line item in module one essentially doing if date between promotion dates and then sum that, if not can use timesum but without any dates - can also then remove sum from the time module...

Looks like timesum doesn't work on "date" as it's not a real time period

Andrew

Highlighted
Regular Contributor

Re: Using a date range in a SUM

Hi

Sorry Andrew, i am now completely lost, this is seeming to be far too complex, for what seems to be a simple thing to do.

I will continue to export to good old Excel and import, as it works.

thanks for the help

 

Highlighted
Master Anaplanner/Community Boss

Re: Using a date range in a SUM

DeveloperCYT,

 

Hang with me as this will be kind of a long post, but I did get it working.

 

Data by Product and Date (real Time):

2018-11-13_10-27-18.png

Result:

Module by Product entering a Date (day value)

 

2018-11-13_10-28-45.png

 

There are two ways to do it, sum it by Time Period (using TimeSum) or summing it by the actual days requested.  Both use a "Fake Time" list with the members being a representation of the Day:

 

 

2018-11-13_10-31-28.png

 

Create a Properties/Attribute module with the list being the Fake Time list (above) with one line item: Date.

 2018-11-13_10-33-05.png

 

2018-11-13_10-33-24.png

 

If you want to use TimeSum (or sum it by Period), you can add three line items to what I called the Product Sum module (2nd picture): Period Start, Period End, and Total

2018-11-13_10-37-30.png2018-11-13_10-37-43.png

This sums the data up to the week (parent of the day) as TimeSum does not work at the day level.

 

If you want to get the "true" sum, create a module with the Product list and the Fake Time with the following line items:

Data and Product List.2018-11-13_10-40-35.png

 

2018-11-13_10-40-14.png

 

 

Formulas being:

  • Data: Product Qty.Data[LOOKUP: Fake Time Properties.Date]
  • Product List: IF Product Sum.Start Date <= Fake Time Properties.Date AND Product Sum.End Date >= Fake Time Properties.Date THEN ITEM(Products) ELSE BLANK

Go back to the Product Sum module and add a line item: Fake Time Sum with this formula: Product by Fake Time.Data[SUM: Product by Fake Time.Product List]2018-11-13_10-44-46.png

 

 

So the result now, you can change the date for the Product, and it automatically sums the total.2018-11-13_10-45-51.png

 

2018-11-13_10-46-20.png

 

 

Hope this helps,

 

Rob

 

 

Highlighted
Regular Contributor

Re: Using a date range in a SUM

Rob, that has helped a huge amount
Thank you so much to you all
Onward and Upward