5 Replies Latest reply on Dec 5, 2016 10:59 AM by Cambria Brown

    Help excluding null values from calculated field denominator

    Cambria Brown

      I have a dataset that includes several dimensions that are dates of when various activities happened.  I created a calculated field that determines if a particular activity happened by its deadline with the following formula:


      Commitment Docs Sent to Contractor by deadline =

      iif([Date Contracts Sent Contract/PO to Entity]<=[Deadline - contract sent to entity], 'Yes', 'No')


      It did what I wanted: Yes if deadline met, No if deadline not met and null if there is no date entered for that activity yet.


      Now what I want to do is calculate the % of records for which the deadline was met, but I want to exclude the records for which there is no date yet entered in that column.  I did this with the following formula:


      % of Commitment Documents sent to Contractor by deadline =

      (COUNT([Commitment Docs Sent to Contractor by deadline])/[Number of docs])


      The problem is that this causes the "nulls" to be counted as "no, deadline not met" instead of excluding them since it's just that the activity hasn't happened yet, it's not that the deadline wasn't met.  Can anyone help me figure out a way to exclude the nulls from this "% of" calc altogether?


      Thank you!