2 Replies Latest reply: Aug 5, 2011 5:03 AM by Chandrashekar Jakkula RSS

    Expression

      Dear All,

       

      I Want to diaplsy the Closing Value of an item bases on the user input AS OF DATE

       

      For Example User Entered AS OF DATE as 27-APR-2011

       

      Then In My Pivot Table it should give closing values as

       

      ITEM_CODEClosing Value
      TTEF2001    130,092.26
      TTEF2004         6,858.08

       

      Closing Value = NEW_QUANTITY * ACUTAL_COST ( The Condition is transaction date should be <= as of date and transaction_id is max )

       

      MY Table Data is pasted below

       

       

      ITEM_CODETRANSACTION_DATETRANSACTION_IDACTUAL_COSTNEW_QUANTITY
      TTEF200131-Mar-20113343353                  18.24 1292
      TTEF200131-Mar-20113345529                  18.24 1532
      TTEF200111-Apr-20113713122                  18.24 1531
      TTEF200111-Apr-20113715422                  18.20 8731
      TTEF200115-Apr-20113773876                  18.21 8635
      TTEF200118-Apr-20113818303                  18.21 8395
      TTEF200119-Apr-20113847769                  18.21 8393
      TTEF200121-Apr-20113881203                  18.21 7625
      TTEF200127-Apr-20113972880                  18.21 7440
      TTEF200127-Apr-20113972881                  18.21 7385
      TTEF200127-Apr-20113972882                  18.21 7145
      TTEF200128-Apr-20114009944                  18.21 6617
      TTEF200128-Apr-20114013965                  18.21 6137
      TTEF200129-Apr-20114042139                  18.20 10169
      TTEF200129-Apr-20114044546                  18.20 9689
      TTEF200129-Apr-20114045806                  18.20 8729
      TTEF200129-Apr-20114047560                  18.20 8249
      TTEF200129-Apr-20114048444                  18.20 7049
      TTEF200130-Apr-20114059263                  18.20 7001
      TTEF200130-Apr-20114080851                  18.20 6761
      TTEF200130-Apr-20114082582                  18.20 6377
      TTEF200104-May-20114152570                  18.20 5897
      TTEF200105-May-20114161593                  18.24 6473
      TTEF200106-May-20114172393                  18.21 6281
      TTEF200109-May-20114279156                  18.21 5321
      TTEF200109-May-20114279311                  18.21 4608
      TTEF200109-May-20114279312                  18.21 4361
      TTEF200113-May-20114432702                  18.21 4217
      TTEF200120-May-20114529102                  18.21 3497
      TTEF200124-May-20114583619                  18.20 5465
      TTEF200125-May-20114602800                  18.20 4985
      TTEF200125-May-20114603677                  18.20 4889
      TTEF200125-May-20114606494                  18.20 4505
      TTEF200126-May-20114622333                  18.20 8537
      TTEF200128-May-20114664510                  18.20 8057
      TTEF200128-May-20114665313                  19.00 12089
      TTEF200130-May-20114702985                  19.00 13625
      TTEF200131-May-20114749462                  18.53 13145
      TTEF200131-May-20114757639                  18.53 12425
      TTEF200131-May-20114758658                  18.53 12144
      TTEF200131-May-20114758659                  18.53 12089
      TTEF200131-May-20114762545                  18.53 12041
      TTEF200131-May-20114773505                  18.53 11568
      TTEF200131-May-20114773506                  18.53 11561
      TTEF200131-May-20114775063                  18.53 10601
      TTEF200431-Mar-20113343677                  17.02 405
      TTEF200419-Apr-20113847770                  17.02 403
      TTEF200428-Apr-20114009765                  16.80 5155
      TTEF200428-Apr-20114009945                  16.82 4752
      TTEF200428-Apr-20114009946                  16.82 4627
      TTEF200428-Apr-20114013966                  16.82 3955
      TTEF200429-Apr-20114044547                  16.82 3235
      TTEF200429-Apr-20114045807                  16.82 2035
      TTEF200429-Apr-20114047016                  16.82 1075
      TTEF200429-Apr-20114047561                  16.82 595
      TTEF200429-Apr-20114049764                  16.82 355
      TTEF200430-Apr-20114059264                  16.82 259
      TTEF200413-May-20114432703                  16.82 115
      TTEF200431-May-20114746642                  19.00 4915
      TTEF200431-May-20114747300                  19.00 9715
      TTEF200431-May-20114749463                  18.97 9600
      TTEF200431-May-20114749464                  18.97 8035
      TTEF200431-May-20114757640                  18.97 7315
      TTEF200431-May-20114758660                  18.97 6355
      TTEF200431-May-20114760007                  18.97 5635
      TTEF200431-May-20114762546                  18.97 5587
      TTEF200431-May-20114773507                  18.97 5011
      TTEF200431-May-20114774051                  18.97 4800
      TTEF200431-May-20114774052                  18.97 4771
      TTEF200431-May-20114775064                  18.97 3811