Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi,
I tried to build an example using your date, please look at the attached.
Regards,
Stefan
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
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