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

    Expression

      Dear All

       

      My Data Table Contains Data as below

       

      TRANSACTION_DATEPRIMARY_QUANTITYNEW_COSTSUBINVENTORY_CODETRANSACTION_IDITEM_CODE
      9-Aug-11-50100.3789971530 - CFB606766030196037
      30-Jul-11100.3789971586083330196037
      30-Jul-1110099.52769275723 - STR586082830196037
      30-Jul-11100.7797641585334130196037
      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
      TOTAL23023087.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)

       

        • 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.