7 Replies Latest reply: Jan 11, 2013 9:58 AM by Sean (Hyunku) Cho RSS

    Help with Set Analysis

      Hi Community:

      I have some issues calculating in set analysis.

      This is my data. I have many transacionID per contract. What I'm trying to do is to get the Amount on the Max of TransactionID, within the date range (date entered by an user) and when ObjectID = 615. So the red boxes are the Max TransactionID between 10/1/2011 and 9/30/2012.

      Data.png

       

      I have calculated the Max of the TransactionID like this:

       

       

      Max({<Date={'>=$(vStartDate)<=$(vEndDate)'}>}TransactionID)

       

      This works fine, but then I want to get the Amount including the above set analysis and does not work.

       

      data2.png

       

      I have tried 2 options:

      1. This calculation give me the results only when clicking a Contract, and that is not what I'm looking for. I want to always display the values

        Sum ({<TransactionID={$(=Max({<Date={'>=$(vStartDate)<=$(vEndDate)'}>}TransactionID))}, ObjectID={615}>}Amount)

       

      2. This gives me something else but not the solution

      Sum ({<TransactionID={"=Max({<Date={'>=$(vStartDate)<=$(vEndDate)'}>}TransactionID)"}, ObjectID={615}>}distinct Amount)

       

       

      The expected resultd should be:

      ContractAmount
      20101214450.00
      01297240.00
      0140259100.00
      0141061250.00
      0141731500.00
      01537240.00
      0155313950.00

       

      //0129724 is 0 because does not satisfy the condition of Obj=615 on the Max. Transaction ID which is 1003

       

      Any ideas? Attached are my files.

       

      Thanks!