Set Analysis / IntervalMatch / something else?

Thu, 24 Mar 2011 14:25:05 GMT

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

topic Set Analysis / IntervalMatch / something else? in QlikView

Set Analysis / IntervalMatch / something else?