Hello All Qlikview Experts,
I have a query related to condition sum.
Consider I have 3 Tables
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).
