Expression

[#1]

Dear All

My Data Table Contains Data as below

TRANSACTION_DATE	PRIMARY_QUANTITY	NEW_COST	SUBINVENTORY_CODE	TRANSACTION_ID	ITEM_CODE
9-Aug-11	-50	100.3789971	530 - CFB	6067660	30196037
30-Jul-11		100.3789971		5860833	30196037
30-Jul-11	100	99.52769275	723 - STR	5860828	30196037
30-Jul-11		100.7797641		5853341	30196037
30-Jul-11	50	100.0266872	530 - CFB	5853332	30196037
2-Jun-11	-50	101.3558667	723 - STR	4807186	30196037
31-Mar-11	130	101.3558667	723 - STR	3540691	30196037

The Out Put Should Be

ITEM_CODE	SUBINVENTORY_CODE	Closing Quantity	Closing Value
30196037	530 - CFB	50	5018.949855
30196037	723 - STR	180	18068.21948
	TOTAL	230	23087.16933

the conditions are

1. The transaction_date shold be <= User Entered As of Date in Input box ( For Above Example Entered as 30-JUL-11)

2. Closing Quantity = Sum(PRIMARY_QUANTITY)

3. closing Balance = Closing Quantity * NEW_COST ( But New Cost Should Be Last Transaction id's and <=30-jul-11 for that particular item)

[Anonymous]

Hi Chandu

Is your Transactions_Date by any chance a time stamp that you converted into date only format or do you have an index so that you could sort the table and pull out the last transaction id for example if  the ID is incrementative you could use a function like firstsortedvalue to pull through the correct information but the data needs to be sorted correctly in the first instance.