2 Replies Latest reply on Jun 2, 2017 3:07 PM by taylor.johnson

    Aggregate but exclude if field contains 'x'




      I am trying to calculate a field called LTV (Loan-to-Value).  In the TWBX this is simply calculated by taking the Loan Amount / Appraised Value.  This determines the LTV.


      I am using a simple AGG(Loan-to-Value) to determine this for the sub totals and grand total rows. 


      What I want to do is use the AGG LTV function but I want to exclude the the property from this calculation IF the Debt Type=Credit Line.  Conversely, if the Appraised Value=0, then it should be excluded from the AGG LTV as well.  Either of these conditions would be fine (I do not need both), just whatever is easier.


      Currently: The sub total LTV in Status=In Progress is  (40+26+41+90 / 66+43+58+0)111% which doesnt make sense (this is the sum of all the properties loan amounts / divided by appraised amounts.

      Proposed:  The sub total LTV in Status=In Progress would display (40+26+41 / 66+43+58) =  64%