7 Replies Latest reply: Apr 27, 2018 3:33 AM by Quy Nguyen RSS

    Set analysis by field in other table

    Quy Nguyen

      Hi All,

       

      I have a simple application with below data model:

      Capture.PNG

      I want to create a simple table show: EmployeeID and Sum(Amount) based on TradeDate selection.

      For each Employee: Sum of Amount should be calculated from Min(TradeDate) to Max(TradeDate) in selection, but if Min(TradeDate) < WorkInDate (the day employee starts to work), Sum of Amount should be calculated from WorkInDate to Max(TradeDate) and all the amount from Min(TradeDate) to WorkInDate should be remove from calculation.

       

      I used this expression but got wrong result:

      SUM({<TradeDate={">=$(=Date(Max(WorkInDate)))<=$(=Date(Max(TradeDate)))"} >}Amount)


      I also attach here qvf and data sample files. Please help me to solve it. Thank you.


      Bests,

      Quy