2 Replies Latest reply: Mar 31, 2016 6:44 AM by Mo Abdolrahim RSS

    How can I filter by max date of a particular date column?

    Gatter _

      I have data within the table view that has like 10 columns worth of data.  One of those columns contains data related to receipt date.  The source report though is cumulative.  I am trying to find a way to filter all the data from the report to only data related to the max date within the receipt date column.  How can I do this?  I've tried creating a filter and using the max expression, but it is not working.

       

      Thanks,

      Gatter

        • Re: How can I filter by max date of a particular date column?
          Olly Bond

          Hello Gatter,

           

          Monarch can't "see" data from another row when it's working in the table - for example when evaluating a filter or defining a calculated field. It's not Excel, where you can define cell A3 to be the maximum of A1 and A2.

           

          But you can do this - using two steps. First, make a summary, where the Max(Date) function will work as a measure. You'll need at least one key field - customer number, account number, recno() would all work.

           

          Then export this summary as a file - either Excel or Access should work fine.

           

          Then in the table, define an external lookup where you join your table data on the key field to read in the max(date) you need.

           

          Then you have the relevant max(date) defined in each row and can filter as needed.

           

          Best wishes,

           

          Olly

            • Re: How can I filter by max date of a particular date column?
              Mo Abdolrahim

              Hi Gatter,

              You may try the following solution and see if works for you.

              I am assuming your data contains duplicate receipt number, and each receipt number had a different receipt date.

              Let's assume we have a file with three fields, and we would like to show only those rows with the highest ship date.

              orderno,shipdate,amount

              536251,3/26/2010,1000

              536251,3/25/2010,999

              536251,3/24/2010,888

              536250,3/23/2010,1001

              536250,3/22/2010,1002

              536250,3/21/2010,1003

              536249,3/26/2010,1001

              536249,3/25/2010,1002

              536249,3/22/2010,1005

              First create a sort by ship date in descending order.

              then create a filter by order no, use Advanced tab and select order no, and select unique rows, duplicate row, and only one row.

              On the table view apply the sort and filter and you end up with those rows with the highest ship date.