0 Replies Latest reply: May 12, 2010 10:26 AM by bresman RSS

    SET ANALYSIS - USING MULTIPLE FILTERS ON SINGLE TABLE

    bresman

      I have an Item transaction (ledger) table where I record transaction dates, manufacturing center, main products, components and qty.

      How do I use Set Analysis to filter the list of main products and components for an evaluation period whilst ignoring both the period and manufacturing center?

      I want to calculate inventory movement to date for the product, irrespective of the manufacturing centre and period.

       

      Year

      Month

      Product

      Open. Bal.

      Movement for Period

      Closing Bal.

      2006

      8

      XXXXXXXX

      A.

      B.

      C.

       

       

       

       

       

       



       

      A. I want to calculate the opening balance based on ALL movements in the table for product XXXXXXXX where the trx date is less than the first day of 08/2006.

      · =SUM({$<[Year]= ,[Month]=,[Main Product]= ,[Component]=, [Manufacturing Center] =,[Posting Date]={"<$(=MAKEDATE([Year],[Month],1))"}>}[Qty])

      B. I want to calculate the movement during the period 08/2006.

      · =SUM({$<[Main Product]= >}[Qty])

       

      C. I want to calculate the closing balance for product XXXXXXXX by summing all movements in the table up until the last day of 08/2006

      · =SUM({$<[Year]= ,[Month]=,[Main Product]= ,[Component]=, [Manufacturing Center] =,[Posting Date]={"<$(=MONTHEND(MAKEDATE([Year],[Month],1)))"}>}[Qty])

      D. Product XXXXXXXX must be filtered based on the Manufacturing Center, and Transactions that have been posted during the period 08/2006

      · By using the "[Component] = " in the calculating the O/B the result lists ALL components.