11 Replies Latest reply: May 15, 2014 9:52 AM by Nick Osdale-Popa RSS

    Filtering most recent dates

    Phillip _

      Hi

       

      I have lists of dated transactions related to multiple instances of the same item code numbers. I need to find the most recent instance by date. How can I filter the date to show only the most recent item please?

       

      Thanks

      PhillipS

        • Filtering most recent dates
          Phillip _

          Hi

           

          I have lists of dated transactions related to multiple instances of the same item code numbers. I need to find the most recent instance by date. How can I filter the date to show only the most recent item please?

           

          Thanks

          PhillipS

          • Filtering most recent dates
            Steve Caiels

            Hi,

             

            I hope you have V7?

             

            If so, you can do this in the advanced tab of the filter.

             

            You need to the table by item code then descending date as a 2nd level.  Then create a filter showing only those records that are unique with respect to item code.

             

            Cheers

            Steve

            • Filtering most recent dates
              Steve Caiels

              Hi,

               

              I hope you have V7?

               

              If so, you can do this in the advanced tab of the filter.

               

              You need to the table by item code then descending date as a 2nd level.  Then create a filter showing only those records that are unique with respect to item code.

               

              Cheers

              Steve

              • Filtering most recent dates
                Phillip _

                Thanks Steve, this is very interesting.

                 

                However, despite sorting by item and then by descending date the result shows the oldest date not the most recent.

                Unfortunatly the same thing happens when sorting dates ascending.

                Is this anything to do with the English date format of DD/MM/YYYY ?

                 

                Phillip

                • Filtering most recent dates
                  Phillip _

                  Thanks Steve, this is very interesting.

                   

                  However, despite sorting by item and then by descending date the result shows the oldest date not the most recent.

                  Unfortunatly the same thing happens when sorting dates ascending.

                  Is this anything to do with the English date format of DD/MM/YYYY ?

                   

                  Phillip

                  • Filtering most recent dates
                    Steve Caiels

                    Hi,

                     

                    I've just tried it, and you're right.  I don't think it is an issue with the UK date format.

                     

                    There is a workaround if you have Monarch Pro. You could sort the table as required, export as a database format (Excel, Access etc) then join it back in on the ID.  You'll get a warning that the database contains multiple matches for each ID and the join will bring in the first match. This will be the most recent date. You could then filter on unique ID and hide the original date column.

                     

                    Regards

                    Steve.

                    • Filtering most recent dates
                      Steve Caiels

                      Hi,

                       

                      I've just tried it, and you're right.  I don't think it is an issue with the UK date format.

                       

                      There is a workaround if you have Monarch Pro. You could sort the table as required, export as a database format (Excel, Access etc) then join it back in on the ID.  You'll get a warning that the database contains multiple matches for each ID and the join will bring in the first match. This will be the most recent date. You could then filter on unique ID and hide the original date column.

                       

                      Regards

                      Steve.

                      • Filtering most recent dates
                        tcorley _

                        Sort By Date then filter rowno() = 1

                        • Filtering most recent dates
                          tcorley _

                          Sort By Date then filter rowno() = 1

                          • Filtering most recent dates
                            Nick Osdale-Popa

                            Originally posted by tcorley:

                            Sort By Date then filter rowno() = 1 /b[/quote]That won't work:

                            A filter may not depend on the Rowno() function either directly or indirectly.

                            • Filtering most recent dates
                              Nick Osdale-Popa

                              Originally posted by tcorley:

                              Sort By Date then filter rowno() = 1 /b[/quote]That won't work:

                              A filter may not depend on the Rowno() function either directly or indirectly.