1 Reply Latest reply: Aug 12, 2011 4:17 AM by Shay Cooper RSS


      Dear All


      My Data Table Contains Data as below


      9-Aug-11-50100.3789971530 - CFB606766030196037
      30-Jul-1110099.52769275723 - STR586082830196037
      30-Jul-1150100.0266872530 - CFB585333230196037
      2-Jun-11-50101.3558667723 - STR480718630196037
      31-Mar-11130101.3558667723 - STR354069130196037


      The Out Put Should Be


      ITEM_CODESUBINVENTORY_CODEClosing QuantityClosing Value
      30196037530 - CFB505018.949855
      30196037723 - STR18018068.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)


        • Expression
          Shay Cooper

          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.