Inventory Report Problem

Thu, 20 Jun 2013 07:50:23 GMT

I have a problem in getting the Beginning Inventory. Table Details.

1. I have a History Trail Table. It contains Item No., On-hand Quantity, Begin Date and End Date.

2. There are multiple Records per Item No. (but different Begin Date)

What I want to do:

1. First is to filter the records by the Selected Date. All record that have Begin Date less than my Selected Date.

2. Then get the Items On-hand Quantity, (only one record per Item No. and only the latest record per Item. I want to get the Item Record based on the rescent begin date of the Item.)

Item_# Begin_date End_Date OH_QTY STORE_NO

1 2013-01-05 2013-02-05 10 1

1 2013-04-01 2013-05-01 5 2

1 2012-05-01 2012-07-06 10 1

2 2013-06-01 2013-07-19 50 2

2 2013-01-24 2013-03-10 13 1

3 2011-06-05 2013-06-29 10 1

3 2012-05-01 2013-02-17 7 1

3 2013-01-01 2013-10-12 60 2

I want to get all items with begin_date < '2013-05-27'. the TOTAL On-hand Quantity should be 30.. I need to get the Highlighted Records above. What is the set analysis to be used?

topic Inventory Report Problem in QlikView

Inventory Report Problem