11 Replies Latest reply: May 15, 2014 10:04 AM by Gareth Horton RSS

    most recent date

    bgoderwis _

      I'm sure I have seen this topic discussed before, but I can't seem to find the right thread.

       

      I have a very simple report: doctors' name, patients' name, office visit date.  All three elements are on each line.  The data goes back several years, so any given patient could have seen the same doctor multiple times.  For each doctor/patient combo, I would like to capture the most recent date. 

       

      I know I'm making this harder than it is.

       

      Thanks.

        • most recent date
          Olly Bond

          Hello bgoderwis,

           

          You should be able to do this in one model by:

           

          1. creating a Sort order which sorts your data by Doctor/Patient combination and then by Date, making sure that the Date is in newest-to-oldest order.

           

          2. creating a Filter that has an empty expression in the normal tab, but in the Advanced tab selects "one row only" for duplicated rows, ticking the Doctor and Patient fields to be checked for uniqueness.

           

          Best wishes,

           

          Olly

            • most recent date
              bgoderwis _

              Hi Olly,

               

              Thanks for the quick reply.  Your solution is very, very close to what I need.  However, the filter gives me the earliest (lowest) date each time, regardless of the sort order, which I find pretty strange.

               

              Bob

                • most recent date
                  Nigel Winton

                  Bob

                  I get at this from a different direction.

                  Create a calculated field for the number of days in Today - the Visit Date. (today() - 'your date')

                   

                  Then create a summary with the measure being Min(The calculated Field). That will give you the last date visited.

                   

                  I have gone further to set up a project export to send the summary out to an Excel sheet. This is then linked back into the same model and a filter set up to only show the items in the table where the calculated days is equal to the new imported field. You need to refresh lookups next to get the values correct. Then you can export your table using a project and specifying the filter there. At all times make sure you do not have the table filtered or you will get some surpirsing results.

                   

                  If you need any more help let me know.

                   

                   

                  Regards

                   

                  Nigel

                    • most recent date
                      bgoderwis _

                      Nigel,

                       

                      Your way worked like a charm!  Thanks for your help!

                       

                      Bob

                      • most recent date
                        MCJoy _

                        Bob

                        I get at this from a different direction...

                         

                        Then create a summary with the measure being Min(The calculated Field). That will give you the last date visited...

                         

                        If you need any more help let me know.

                         

                         

                        Regards

                         

                        Nigel[/QUOTE] As directed by my hubby (Nick Osdale-Popa) I came out here to find an answer to this identical problem. But we couldn't get the summary to work because if you include the original date in the Key section your records are no longer unique and if you don't include this field then you're left wondering what the actual date is that corresponds to the calc'd min date field.

                         

                        What step might we be missing?

                         

                        He created a work-around building on the first step of creating the calc'd min date.

                        1) created a calc'd field that reflected a combination of the two keys (in the above case it would be doctor+patient)

                        2) created a summary keying on the calc'd combo field and measuring on the calc'd min date

                        3) created a calc look-up table copying/pasting the data from the summary

                        4) created a filter where the calc'd date = the calc'd min date

                         

                        Please let us know if there is in fact an easier way. I ran into the same problems as the above with V9.01 not sorting the dates and returning the oldest date no matter what option I chose. So that solution didn't work.

                         

                        Thanks for your time with this issue.

                          • most recent date
                            Olly Bond

                            Hello MCJoy,

                             

                            It looks like Monarch is applying the unique filters before applying the sort order, as you and Bob are reporting the same results.

                             

                            In two stages, then, sort the table so that the most recent dates are at the top of the list, and export that data to an .mdb file. Stage two, read this in, and apply the unique filter on the relevant keys.

                             

                            Best wishes,

                             

                            Olly

                              • most recent date
                                Nigel Winton

                                Hello McJoy

                                In my method I create the calculated field in the table to get the number of days different from your date to today or any other date you decide. Formula Today()- 'Your Date' will give a number of days elapsed Call it 'Elapsed Days'. There is no other calculated field needed.

                                 

                                Use the 'Elapsed Days' as the measure in a summary,then change the formula for the measure to Min from the default Sum. This only shows the latest date for the record. The key fields can be whatever you need, if you have a sequence number or case number, or in my case a part number that is good. So for every part number I only get one record in the summary and this is the latest date.

                                The second part of my way is to export this first summary to an Excel Spreadsheet then link it back into the original model, so for each part number you get the same Min(Days Elapsed) number, filter this where the original calculated field is the same as the linked filed and you get the latest entry for each part.

                                You need to refresh links after you export the summary.

                                 

                                You must have filters and sort set to none when doing this as they will give you some unexpected results. I apply them either in a summary or a project export.[/B]

                                 

                                If you need any other help, let me know.

                                 

                                Regards

                                 

                                Nigel

                                  • most recent date
                                    MCJoy _

                                    Thanks everyone for the additional info. I think what was tripping me up was that I wanted to include the date in my summary and this cannot be done. The summary will indeed return the most recent event but you just wouldn't know what that date was. In my case it's okay as the actual data I'm trying to isolate is the most recent pay rate and it doesn't matter what[/I] the effective date is as long as I capture the most current one.

                                     

                                    Just for academics sake, what if the date field was in fact what you needed to isolate? As in the case of the doctor/patient/date of office visit, what if you needed to know the actual date of the visit?

                                     

                                    Using the summary you'd only see the number that represents the "Elapased days" vs. a true date. I understand you'd end up with a condensed file of only the most current office visits but it would be: doctor/patient/# (equal to the minimun of the calc'd field). How could you get that back to the actual date of the visit?

                                     

                                    Thanks again!

                                    Lisa

                                      • most recent date
                                        Olly Bond

                                        Hello Lisa,

                                         

                                        A calculated field like Today()-Elapsed should return the original Date, or use an external lookup to the original table. That might require an extra dummy constant field to be created and included in the summary, as you'd want to match on all three fields.

                                         

                                        HTH,

                                         

                                        Olly

                            • most recent date
                              Gareth Horton

                              Olly is right.  In V9, this was the case.  In V10, we enhanced this so that the sort was applied first.

                               

                              If you notice, there is a small change in the Advanced Filter dialog, in the Duplicated Rows checkbox area.  It now reads "first row only, as sorted".

                               

                              This makes this type of operation much easier than having to revert to the summary approach.

                               

                              Gareth

                               

                              Hello bgoderwis,

                               

                              You should be able to do this in one model by:

                               

                              1. creating a Sort order which sorts your data by Doctor/Patient combination and then by Date, making sure that the Date is in newest-to-oldest order.

                               

                              2. creating a Filter that has an empty expression in the normal tab, but in the Advanced tab selects "one row only" for duplicated rows, ticking the Doctor and Patient fields to be checked for uniqueness.

                               

                              Best wishes,

                               

                              Olly[/QUOTE]

                            • most recent date
                              Nigel Winton

                              Lisa

                              If you check out the second part of my method, you can export the first summary to an external file, Excel, and then link this back into the same report and model.

                               

                              Then you can filter the table where the Min(Elapsed Days) which you have just drawn in and the original Elapsed Days are the same. This will give you all of the data for the latest date.

                               

                              All you need is a suitable link, such as patient number for the first summary.

                               

                              Effectively this is a 2 pass solution in one Project.

                              You must remember the no filter or sort rule on the table and refresh the link after you export the summary with the Min(Elapsed Days) field.

                               

                              Filters and Sorts can be applied to exports and summaries as required.

                               

                              I do have a more detailed explanation, if you PM me your email address I will send it to you.

                               

                              Regards

                               

                              Nigel