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.
