0 Replies Latest reply: Mar 24, 2011 10:25 AM by Dror Svartzman

# Set Analysis / IntervalMatch / something else?

Good people,

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