4 Replies Latest reply on Apr 27, 2018 2:42 PM by David Núñez

    Latest repair orderfilt

    David Núñez

      Hi everyone.

       

      I work at a car workshop. We have a list of VINs and they could come at different times for their maintenances, according to their mileage. I’d like to filter out the latest order date, repair order ID and each RO detail for each VIN, is this possible?

       

      thanks in advance.

       

      David

        • 1. Re: Latest repair orderfilt
          Jennifer VonHagel

          Hi David, it would help to see more about how your data is structured, but yes, this is possible.

           

          Is it correct to say that for each VIN, you want to find the records where the order date is the most recent of all the VIN's order dates?  If so, you can create a Filter Calculation something like:

           

          [repair date] = { FIXED [vin] : MAX([repair date] }

           

          You can see how that works below. I've put Customer, Order Date, Order ID in a table. The calculation checks for records where the order date = the highest order date for each customer. The FIXED part lets you specify the Level of Detail (LOD) across which to find the max.

           

          So then put this calculation on the Filter card, set it to TRUE, and add any detail records of the service order to your table you like:

           

          The FIXED formula will ignore other filters in the view (say you filter by customer type or appointment type - the FIXED formula will not reflect that). If this is an issue there are workarounds.

           

          Hope this helps,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Latest repair orderfilt
            David Núñez

            Hi Jennifer,

             

            Thank you very much. This works great!

             

            Best regards,

            David

            • 3. Re: Latest repair orderfilt
              Jennifer VonHagel

              You're welcome!  If you find you need to add some filters to your data, this might help:

               

              FIXED formulas will disregard filters in your data. So say you filter this example to show only the year 2014, Aaron Bergman's Max date will still show as 11/10/2016.

               

              To get around this, filters that should apply to FIXED formulas need to be added to context:

              And now the FIXED formula will find the max only within year 2014.

              1 of 1 people found this helpful
              • 4. Re: Latest repair orderfilt
                David Núñez

                Hi Jennifer,

                 

                This is really interesting and helpful, as a matter of fact I was figuring out how to do this. Thank you very much, you're really kind.

                 

                Best regards,

                David