1 Reply Latest reply: May 15, 2014 10:10 AM by Data Kruncher RSS

    Filter

    ChristyJ _

      I'm having a problem creating a filter for a report. My report contains Names and Due dates. I need to filter the Names and Due Date to only show the Last due date for the name. Example of the report is below:

       

      2008-06-15      1 EQUIFAX

      2008-07-17      1 EQUIFAX

      2008-08-16      1 EQUIFAX

      2008-09-15      1 EQUIFAX

      2008-10-15      1 EQUIFAX

       

      Thanks for the help.

        • Filter
          Data Kruncher

          Hi Christy,

           

          This is much easier to do with v10 than it is with v9.

           

          With v10, you'd first setup a sort order, probably using the name (ascending) and then the date (descending). Next you create a filter with the Advanced tab set to display duplicated rows, "first row only, as sorted". Also elect to display unique rows.

           

          v9 doesn't offer the "as sorted" option, so you typically need a solution that involves two models, and you need v9 Pro because you need to build an external lookup. In the first model, build the same sort and export the table to a temporary file.

           

          Now in a new model open your report and build an external lookup to the temporary file just exported, connecting on the name, and bringing in the date as LastDate. Monarch will bring in the date for the first record that it finds that matches the name it needs. Because of your sort order, that will be the last date.

           

          Now build a filter to display records where the report date is equal to LastDate.

           

          You can create a batch file to run both models to make this easier to manage if it will be something that you'll need to do fairly regularly.

           

          HTH,

           

          Kruncher