Grouping item level

[#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 Qty	Cost	Closing Value
30196037	530 - CFB	50	100.3789971	5018.949855
30196037	723 - STR	180	100.3789971	18068.21948

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)

[swuehl]

Hi,

I tried to build an example using your date, please look at the attached.

Regards,

Stefan

[#3]

Dear Stefan,

Thansk for your reply as adviced you i writen the Expresion but the answer is coming with near to the required ans.

Files has been attached to understand my problem.

in attached file as per my expression the total is coming correct but in table item level its giving multiple rows. for exact error attached support error file (xlsx) with records Yellow background

pls provide a solution

Thanks & Regards

Chandu

[swuehl]

I think this should look better, though I haven't seen in your QV app that you tried my original suggestion.

I just plugged in my expression in your app, but then struggled a little with date / timestamp formatting and variables.

Hope this helps,

Stefan