Table11 contains data for a product (ProductId), LookbackSalesWeeks & LookbackSalesDays. This is a strategic table where user specifies, how many weeks of sales history they want depending on product category.
Product Category
LookbackSalesWeeks
LookbackSalesDays
A1
2
14
A1
2
14
B1
6
42
B2
15
105
B1
6
42
C2
8
56
Q1
5
35
Q2
10
70
Table 22 is having data relating ProductID to its category and
Table33 is showing sales done in past couple of weeks/Months, with Fields like ProductId, salesdate, and salesweek (weekstart date of monday)
Table22
ProductID
Product Category
4100
A1
4121
Q1
4225
A1
4553
B1
5339
B2
6390
B1
6463
A1
5508
Q1
Table33
ProductID
SalesDate
SalesWeek (LastMonday)
Sales
4100
10/4/2013
9/30/2013
46775.00
4121
10/10/2013
10/7/2013
5345.00
4225
10/22/2013
10/21/2013
746345.00
4553
10/17/2013
10/14/2013
62209.00
5339
11/5/2013
11/4/2013
595782.00
6390
9/12/2013
9/9/2013
34892.00
5339
8/21/2013
8/19/2013
790926.00
6463
11/29/2013
11/25/2013
559925.00
5508
12/12/2013
12/9/2013
98519.00
5339
8/29/2013
8/26/2013
486529.00
Based on the LookbackSalesWeeks / LookbackSalesDays & Product Category, we need to find out the past sales for a product based on days and Product Code combination.
For Example – Product Id “5339” is having Product Category “B2”. So we need to have all these fields (Id, Code, LookBackSales Days & Weeks) in One table with combined sales in last 15 weeks or 105 Days starting from Todays date (Date on which the document is opened, i.e. today, we will need all sales for Product 5339 in last 15 weeks since 26-Aug-2013).
