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

    Set Analysis / IntervalMatch / something else?

    Dror Svartzman

      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

       

      error loading image

       

      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