1 Reply Latest reply on Dec 7, 2017 12:32 AM by Tushar More

    Aggregate MAX date for a filter

    Peter Goett

      The data I am working with is very sensitive, and it would be hard to anonymize, so I haven't attached a twbx, but I can if absolutely needed. Here is my issue though:


      I have a list of projects, and multiple vendors submit invoices to that project. We want to identify any project that hasn't received an invoice, from any vendor, in over 12 months. The data looks something like this:


      Project NameVendor NameInvoice Received Date
      Project ABCVendor 11/28/14
      Project ABCVendor 26/8/15
      Project ABCVendor 39/23/16
      Project ABCVendor 4 3/3/17
      Project 123Vendor 23/5/12
      Project 123Vendor 58/3/14


      My goal would be to see a report of only one line per project, and the MAX overall date, then filter out any that have received an invoice in the past year. So before filtering, the report should look like this:

      Project NameInvoice Received Date
      Project ABC3/3/17
      Project 123


      After filtering, the report should only show Project 123.


      My issue is if I use a filter on MAX[Invoice Received Date], it applies the filter on the row level - filtering out any rows that have a MAX[Invoice Received Date] within the year, so the filtered report comes back looking like:

      Project NameInvoice Received Date
      Project ABC9/23/16
      Project 123