Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good people,
Kindly your advise on this matter...
Consider the following scenatio:
Inventory table:
LOAD * INLINE [
ID, Warehouse ID, Artile SKU, Inventory Date, Inventory Date To, Qty Received, Qty on Stock
1, 100, AB, 24-3-2011, 25-mrt, 23, 24
2, 100, AB, 25-3-2011, 28-mrt, 1, 25
3, 100, AB, 28-3-2011, 1-1-3000, 0, 0
4, 100, AC, 1-1-2011, 31-1-2011, 0, 45
5, 100, AC, 31-1-2011, 6-2-2011, , 34
6, 100, AC, 6-2-2011, 25-2-2011, 23, 57
7, 100, AC, 25-2-2011, 1-1-3000, , 56
8, 100, AD, 2-1-2011, 11-1-2011, ,
9, 101, AD, 11-1-2011, 1-1-3000, ,
10, 101, AB, 22-3-2011, 24-mrt, 23, 24
11, 101, AB, 24-mrt, 28-3-2011, , 22
12, 102, AB, 28-3-2011, 1-1-3000, 2, 24
13, 102, AC, 1-1-2011, 31-1-2011, 0, 68
14, 102, AE, 2-3-2011, 10-3-2011, 300, 300
15, 102, AE, 10-3-2011, 17-3-2011, 50, 350
16, 102, AE, 17-3-2011, 18-3-2011, , 200
17, 102, AE, 18-3-2011, 1-1-3000, 20, 2020
];
ID - Row ID,
Store ID - Store Unique ID,
Article SKU - Article P\N
Inventory Date - Date
Inventory Date To - This row is valid until
Qty Received - Number of articles received
Qty on Stock - Number of article on stock (To date)
This table is afterwards linked through link table (star scheme) to the following tables:
- Articles (Dimension)
- MasterCalendar (Dimension)
- Sales (Transaction)
- Warehouse
What I would like to achieve is:
- current stock level (to date)
1. I tried using set analysis:
sum(1{<
[Inventory Date]= {'<=$(=Date((Max(Date))))'},
[Inventory Date To]= {'>$(=Date((Max(Date))))'}
>} [Qty on Stock])
This gave the desired results but using 1 qualifier means also all other selections in the chart are disregarded.
2. This seems like classic case for extended Intervalmatch, but i couldn't get it to work and eitherway i'm afraid it will the sheer load of records will overload the server
3. Cutting the MasterCalendar from the data model is one way, but also means i would have to implement a set analysis expression for every measure in the solution.
Any idea's?
Dror