3 Replies Latest reply: Aug 29, 2017 4:28 PM by Felip Drechsler RSS

    How do I count missing records based on other existing records?

    Todd Buss

      Perhaps someone could point me in the right direction.

       

      I'm loading a table that consists of report-dates and facility-names.  It's something like this:

       

      FacilityDatepatientdata3, 4, 5, etc
      1011/1/17jones
      1011/1/17smith
      1012/1/17smith
      1022/1/17thomas
      1032/1/17franklin
      1013/1/17jones

      What is supposed to happen is that each month, each facility appends the table with several records.

       

      I need to create a matrix of ALL facilities and ALL dates that shows the number of reports received:

      101102103
      1/1/17200
      2/1/17111
      3/1/17100

       

      This will be used to show which facilities are failing to report.  For example, for the 3 months depicted, Facility 101 submitted 100% and Facilities 102 and 103 submitted 33% of reports.

       

      I think I need to make an aggregate key of facility and date and count the number of records for each, but I don't see how I can make an aggregate key for the records that are missing (the zero values).    I don't want to use set analysis because the dates and facilities are dynamic.

       

      Thanks for reading.  :-)