Using a date range in a SUM

DeveloperCYT

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

 

 

 

rob_marshall

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):

Result:

Module by Product entering a Date (day value)

 

 

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:

 

 

 

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

 

 

 

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

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.

 

 

 

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]

 

 

So the result now, you can change the date for the Product, and it automatically sums the total.

 

 

 

Hope this helps,

 

Rob

 

 

zafkamar

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

DavidSmith

@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

DeveloperCYT

Module with daily salesModule 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

 

 

andrewtye

@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

DeveloperCYT

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)

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

 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

 

andrewtye

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

DeveloperCYT

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

 

DeveloperCYT

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

DeveloperCYT

The problem here is that i could have the same product on promotion 6 times a year

 

So

product 1     01/01/18  to   31/10/18

product 1     01/03/18  to   31/03/18

product 1     01/05/18  to   31/05/18

 

your method will not work with this , as your final module is by product an can therefore only have one line per product with one set of dates..