11 Replies Latest reply: May 15, 2014 9:54 AM by Lynette _ RSS

    Record Filtering

    Christian Jacque

      ear all,

      maybe someone can help with the following issue :

      think of a list of orders with customer #, part #, price and invoice date is there a possibility of extracting only the most recent sales data ? depending on how the sort is made on the invoice date, this would correspond to the selection on "first record" or "last record" of a partciular customer - part combination (some database programs offer this selection.

        • Record Filtering
          Grant Perkins

          Christian,

           

          I haven't tried this and will probably not have a chance to experiment for a few days yet but it sounds like you will have to produce a couple of summaries and look for MIN and MAX dates. (I have assumed that MIN and MAX will work on dates.)

           

          That would give you every order (line?) for a customer based on the last or first order date for the customer from the list. Could be several orders of course.

           

          The process is probably very similar in concept to the aging problem posted by Ralphb. Or at least that could be one solution.

           

          Hope this injects some ideas for you.

           

          Best regards,

           

          Grant

           

            Originally posted by Christian Jacque:

          ear all,

          maybe someone can help with the following issue :

          think of a list of orders with customer #, part #, price and invoice date is there a possibility of extracting only the most recent sales data ? depending on how the sort is made on the invoice date, this would correspond to the selection on "first record" or "last record" of a partciular customer - part combination (some database programs offer this selection. /b[/quote]

           

          [size="1"][ March 25, 2003, 10:06 AM: Message edited by: Grant Perkins ][/size]

          • Record Filtering
            Christian Jacque

            Dear Grant,

            many thanks for your suggestion.

            Unfortunately, calculations do not work on dates as summary fields.

            I have added a calculated field Today()-[Invoice Date] which is a number on which summary Min Max calculations can be done; however this does not resolve my problem.

            If you imagine a list of order details, I would just want to see the most recent for a given customer ones and nothing else.

            I have spent quite some time on it and start to wonder wether it can be done.

            I will work on with your idea and let you know.

            Best regards,

            Christian

             

            Originally posted by Grant Perkins:

            Christian,

             

            I haven't tried this and will probably not have a chance to experiment for a few days yet but it sounds like you will have to produce a couple of summaries and look for MIN and MAX dates. (I have assumed that MIN and MAX will work on dates.)

             

            That would give you every order (line?) for a customer based on the last or first order date for the customer from the list. Could be several orders of course.

             

            The process is probably very similar in concept to the aging problem posted by Ralphb. Or at least that could be one solution.

             

            Hope this injects some ideas for you.

             

            Best regards,

             

            Grant

             

               /size[quote]quote:[/size]Originally posted by Christian Jacque:

            ear all,

            maybe someone can help with the following issue :

            think of a list of orders with customer #, part #, price and invoice date is there a possibility of extracting only the most recent sales data ? depending on how the sort is made on the invoice date, this would correspond to the selection on "first record" or "last record" of a partciular customer - part combination (some database programs offer this selection. /b[/quote][/b][/size][/QUOTE]

            • Record Filtering
              Grant Perkins

              Christian,

               

              Your substitution of number of days for the not-possible direct MIN/MAX on date is the way I would go as well. (I checked the Help files only, nothing specified one way or the other for dates so I expressed caution!)

               

              Your description sounds to be like the same problem that Ralphb has (see Winn's latest post for that one in case you don't have V6 Pro but for a slightly different purpose.

               

              As I see it there is a need to transpose the data from one value (the order or order line detail) to another (Earliest or latest date of last order and vendor number. The Date (or Days back calculation) is the real detail that you require. The first analysis simply transforms the data from the state it is provided in the report to the form you need it for the analysis. At least that's how I see it.

               

              Please keep us informed about how you get on, especially if I have misunderstood something!!

                redface.gif[/img] 

               

              Grant

               

              Originally posted by Christian Jacque:

              Dear Grant,

              many thanks for your suggestion.

              Unfortunately, calculations do not work on dates as summary fields.

              I have added a calculated field Today()-[Invoice Date] which is a number on which summary Min Max calculations can be done; however this does not resolve my problem.

              If you imagine a list of order details, I would just want to see the most recent for a given customer ones and nothing else.

              I have spent quite some time on it and start to wonder wether it can be done.

              I will work on with your idea and let you know.

              Best regards,

              Christian

               

                /size[quote]quote:[/size]Originally posted by Grant Perkins:

              Christian,

               

              I haven't tried this and will probably not have a chance to experiment for a few days yet but it sounds like you will have to produce a couple of summaries and look for MIN and MAX dates. (I have assumed that MIN and MAX will work on dates.)

               

              That would give you every order (line?) for a customer based on the last or first order date for the customer from the list. Could be several orders of course.

               

              The process is probably very similar in concept to the aging problem posted by Ralphb. Or at least that could be one solution.

               

              Hope this injects some ideas for you.

               

              Best regards,

               

              Grant

               

                  /size[quote]quote:[/size]Originally posted by Christian Jacque:

              ear all,

              maybe someone can help with the following issue :

              think of a list of orders with customer #, part #, price and invoice date is there a possibility of extracting only the most recent sales data ? depending on how the sort is made on the invoice date, this would correspond to the selection on "first record" or "last record" of a partciular customer - part combination (some database programs offer this selection. /b[/quote][/b][/size][/QUOTE][/b][/size][/QUOTE]

              • Record Filtering
                Christian Jacque

                Dear Grant,

                I am not sure I have outlined the problem accurately enough.

                I have set up below a sample output for you to get a clear picture :

                 

                (1) Custm # 23  Part # 251  7 pcs Ship 03-15-2003

                (2) Custm # 23  Part # 251  3 pcs Ship 02-25-2003

                (3) Custm # 23  Part # 251  9 pcs Ship 12-21-2002

                (4) Custm # 23  Part # 316  1 pcs Ship 02-13-2003

                (5) Custm # 23  Part # 316  3 pcs Ship 01-07-2003

                (6) Custm # 51  Part # 251  8 pcs Ship 11-18-2002

                (7) Custm # 83  Part # 092  1 pcs Ship 03-24-2003

                (8) Custm # 83  Part # 092  3 pcs Ship 12-15-2002

                 

                This would be the table view of a report sorted on Custm # (ascending) part # (ascending) and ship date (descending).

                If I want the most recent sales in my summary, the only lines that should appear are (1), (4), (6), (7) and nothing else.

                If the sort on the ship date is ascending, the summary would list the oldest orders and only lines (3), (5), (6), (8) should be appearing.

                 

                I hope this helps you understand what I would like to achieve. So far I have not been able to sort it out.

                Maybe you have come across a similar case.

                Best regards,

                Christian

                 

                Originally posted by Grant Perkins:

                Christian,

                 

                Your substitution of number of days for the not-possible direct MIN/MAX on date is the way I would go as well. (I checked the Help files only, nothing specified one way or the other for dates so I expressed caution!)

                 

                Your description sounds to be like the same problem that Ralphb has (see Winn's latest post for that one in case you don't have V6 Pro but for a slightly different purpose.

                 

                As I see it there is a need to transpose the data from one value (the order or order line detail) to another (Earliest or latest date of last order and vendor number. The Date (or Days back calculation) is the real detail that you require. The first analysis simply transforms the data from the state it is provided in the report to the form you need it for the analysis. At least that's how I see it.

                 

                Please keep us informed about how you get on, especially if I have misunderstood something!!

                   redface.gif[/img]  

                 

                Grant

                 

                  /size[quote]quote:[/size]Originally posted by Christian Jacque:

                Dear Grant,

                many thanks for your suggestion.

                Unfortunately, calculations do not work on dates as summary fields.

                I have added a calculated field Today()-[Invoice Date] which is a number on which summary Min Max calculations can be done; however this does not resolve my problem.

                If you imagine a list of order details, I would just want to see the most recent for a given customer ones and nothing else.

                I have spent quite some time on it and start to wonder wether it can be done.

                I will work on with your idea and let you know.

                Best regards,

                Christian

                 

                   /size[quote]quote:[/size]Originally posted by Grant Perkins:

                Christian,

                 

                I haven't tried this and will probably not have a chance to experiment for a few days yet but it sounds like you will have to produce a couple of summaries and look for MIN and MAX dates. (I have assumed that MIN and MAX will work on dates.)

                 

                That would give you every order (line?) for a customer based on the last or first order date for the customer from the list. Could be several orders of course.

                 

                The process is probably very similar in concept to the aging problem posted by Ralphb. Or at least that could be one solution.

                 

                Hope this injects some ideas for you.

                 

                Best regards,

                 

                Grant

                 

                     /size[quote]quote:[/size]Originally posted by Christian Jacque:

                ear all,

                maybe someone can help with the following issue :

                think of a list of orders with customer #, part #, price and invoice date is there a possibility of extracting only the most recent sales data ? depending on how the sort is made on the invoice date, this would correspond to the selection on "first record" or "last record" of a partciular customer - part combination (some database programs offer this selection. /b[/quote][/b][/size][/QUOTE][/b][/size][/QUOTE][/b][/size][/QUOTE]

                • Record Filtering
                  Grant Perkins

                  Christian,

                   

                  I understand the basis of your requirement but maybe not the complete implications of the display you require. I think the most important thing is to work out the best filter options and the display questions can them come later.

                   

                  You don't say which version of Monarch you have. I used 5.02 PRO for this solution.

                   

                  To take your example, if you create a calculated field called, for example, "Days Since Order" and make the formula something like your Today()-[SHIPPED DATE] you have the basis for doing the Min and Max comparison you require.

                   

                  Create a summary with Csutomer Number, Part Number as Keys (and anything else that might be helpful for checking purposes)  and then use "Days Since Order" as both of the 2 SUMMARY fields you require. Set the propreties of one of them to MIN and the other to MAX.

                   

                  At the CUSTOMER/PART NUMBER drill level you will now have a record for the oldest and most recent number of days elapsed for each Customer/Part Number combination.

                   

                  Export this (Export or Cut and Paste if using Excel) to a file. I used Excel. You only want the Customer/Part Number combination level of drill down. Nothing deeper than that for this example.

                   

                  Now go back to your table and  (Assuming you have the function available) make a Database Join to you exported summary. Link on both Customer and Part Number and tag both the MIN and MAX fields to be included on you revised Table.

                   

                  You can now use these fields to filter, either individually for an OLDEST only or MOST RECENT only version of the table or use both at the same time to get a single view of the oldest and most recent shipments. Simply compare the "Days Since order" (maybe that should have been called "Days since Shipped?) value for each record to the MIN or MAX values for the same record.

                   

                  Does this get you any closer to a solution? I do realiose that there are some IFs in this - like the version you have.

                   

                  Another way might simply be to add the MIN and Max calculation and ALL OTHER required data fields to a detailed summary and export that to a text file. You could then open the text file as a new report, define the template and filter in the same way as I described above.

                   

                  On the other hand perhaps someone else has a more complete one stage solution?

                   

                  Hope this helps.

                   

                  Grant

                   

                  Originally posted by Christian Jacque:

                  Dear Grant,

                  I am not sure I have outlined the problem accurately enough.

                  I have set up below a sample output for you to get a clear picture :

                   

                  (1) Custm # 23  Part # 251  7 pcs Ship 03-15-2003

                  (2) Custm # 23  Part # 251  3 pcs Ship 02-25-2003

                  (3) Custm # 23  Part # 251  9 pcs Ship 12-21-2002

                  (4) Custm # 23  Part # 316  1 pcs Ship 02-13-2003

                  (5) Custm # 23  Part # 316  3 pcs Ship 01-07-2003

                  (6) Custm # 51  Part # 251  8 pcs Ship 11-18-2002

                  (7) Custm # 83  Part # 092  1 pcs Ship 03-24-2003

                  (8) Custm # 83  Part # 092  3 pcs Ship 12-15-2002

                   

                  This would be the table view of a report sorted on Custm # (ascending) part # (ascending) and ship date (descending).

                  If I want the most recent sales in my summary, the only lines that should appear are (1), (4), (6), (7) and nothing else.

                  If the sort on the ship date is ascending, the summary would list the oldest orders and only lines (3), (5), (6), (8) should be appearing.

                   

                  I hope this helps you understand what I would like to achieve. So far I have not been able to sort it out.

                  Maybe you have come across a similar case.

                  Best regards,

                  Christian

                  /b[/quote]

                  • Record Filtering
                    Ron Jones

                    I have had the same problems getting only the last date. The only way I have been able to get the last date of sale for a given item by extracting to excel. Take the table view info and export to excel.

                       Go to data Pivot table. Click next until you can see the layout option. Click on it and then you will see all info you have imported.Drag the cust and or item info into the row section. Drag the inv date into the data section. Set the field option to Max of invoice date. You can also drag invoice number into row section and set to count of invoice. Then select finish. Take out the sub totals and then you get a sheet with the last date product was sold and also a count of the number of invoices involved. You can then select the entire sheet and copy then paste special as values and create a new spreadsheet with just the info you wanted. (Just use help in Excel to review the Pivot table functions).

                     

                    Ron Jones If you have any questions email jrj256@attglobal.net[/email]

                    • Record Filtering
                      Winn _

                      Christian,

                       

                      I was intriqued by your problem when I read it yesterday. It seemed to defy any sort of calculated field, filter, sort, or summary that I could throw at it. So I was about to give in to Grant's solution (which wouldn't do us Standard version users much good) when I tried somthing on a whim and it worked. This soluition requires that you add 3 calculated fields (2 formula and 1 lookup), a summary, and a filter to your model. It also requires two passes through the table, but does not require you to export anything to a file. Now for the details.

                       

                      1. As Grant suggested, create a calculated field, let's call it Days Back (my standard name for this field) with a formula of: Today()-[Invoice Date].

                       

                      This next step is one of the keys to this working correctly.

                       

                      2. Create a second formula field. Let's call it Key, because it is one of the keys for this to work. If Customer #, and Part # are character fields, then the formula is:

                       

                      Str(,3,0)

                       

                      Note: if the data dor Customer # or Part # is shorter than the data length for the fields, then you will need to substitute the data length for those fields into this formula:

                       

                      Space(<<Customer # data length>>-Len())Space(<<Part # data length>>-Len())Str(,3,0)

                       

                      This creates a field that is properly formatted so that it can be compared to the lookup field that will be set up in a couple of steps.

                       

                      3. Create a new summary as follows:

                       

                      Key fields:Customer #, Part #

                      Summary fields: MIN(Days Back)

                       

                      Change the width of the MIN(Days Back) column to 3. This is important or else this will not work.

                       

                      Now here is what I tried on a whim and was shocked that it worked.

                       

                      4. Copy the entire summary. Switch back to the Table window.

                       

                      5. Create a calculated lookup field of character type. Let's name it Last Invoice. Set the input field to Key. Click the Add button. Click on the blank cell that appears in the Input value column.

                      Hit Ctrl+V to paste the summary into that column. Set the default value to NO.

                       

                      This is the what makes this work. When I tried this on a whim, I expected that Monarch would either give me an error or give me something that I couldn't use for the lookup.

                       

                      If you did everything correctly, then you should see blanks in the Last Invoice column that correspond to the most recent invoices for each Customer #, Part # combination.

                       

                      6. Set up a filter with the expression:

                       

                      <>"NO"

                       

                      That is it. Let me know if you have any problems with this or have any questions about this model.

                      • Record Filtering
                        Christian Jacque

                        Dear Winn,

                        this is absolutely fabulous.

                        It took me a while to get the Key field and lookup string properly formatted (spaces) and the result is stunning.

                        Thank you very much for this suggestion which I recommend you submit to as many users as possible since I believe I am not the only one in this case.

                        Thaks again and nice week end,

                        Christian

                        Originally posted by Winn:

                        Christian,

                         

                        I was intriqued by your problem when I read it yesterday. It seemed to defy any sort of calculated field, filter, sort, or summary that I could throw at it. So I was about to give in to Grant's solution (which wouldn't do us Standard version users much good) when I tried somthing on a whim and it worked. This soluition requires that you add 3 calculated fields (2 formula and 1 lookup), a summary, and a filter to your model. It also requires two passes through the table, but does not require you to export anything to a file. Now for the details.

                         

                        1. As Grant suggested, create a calculated field, let's call it Days Back (my standard name for this field) with a formula of: Today()-[Invoice Date].

                         

                        This next step is one of the keys to this working correctly.

                         

                        2. Create a second formula field. Let's call it Key, because it is one of the keys for this to work. If Customer #, and Part # are character fields, then the formula is:

                         

                        Str(,3,0)

                         

                        Note: if the data dor Customer # or Part # is shorter than the data length for the fields, then you will need to substitute the data length for those fields into this formula:

                         

                        Space(<<Customer # data length>>-Len())Space(<<Part # data length>>-Len())Str(,3,0)

                         

                        This creates a field that is properly formatted so that it can be compared to the lookup field that will be set up in a couple of steps.

                         

                        3. Create a new summary as follows:

                         

                        Key fields:Customer #, Part #

                        Summary fields: MIN(Days Back)

                         

                        Change the width of the MIN(Days Back) column to 3. This is important or else this will not work.

                         

                        Now here is what I tried on a whim and was shocked that it worked.

                         

                        4. Copy the entire summary. Switch back to the Table window.

                         

                        5. Create a calculated lookup field of character type. Let's name it Last Invoice. Set the input field to Key. Click the Add button. Click on the blank cell that appears in the Input value column.

                        Hit Ctrl+V to paste the summary into that column. Set the default value to NO.

                         

                        This is the what makes this work. When I tried this on a whim, I expected that Monarch would either give me an error or give me something that I couldn't use for the lookup.

                         

                        If you did everything correctly, then you should see blanks in the Last Invoice column that correspond to the most recent invoices for each Customer #, Part # combination.

                         

                        6. Set up a filter with the expression:

                         

                        <>"NO"

                         

                        That is it. Let me know if you have any problems with this or have any questions about this model. /b[/quote]

                        • Record Filtering
                          Nick Osdale-Popa

                          5. Create a calculated lookup field of character type. Let's name it Last Invoice. Set the input field to Key. Click the Add button. Click on the blank cell that appears in the Input value column.

                          Hit Ctrl+V to paste the summary into that column. Set the default value to NO.

                            /quoteYou can do that?!?

                           

                          Whoa, that is just too cool.  That should go in the next Monarch Newsletter!

                          • Record Filtering
                            Winn _

                            Christian,

                             

                            I am glad that I could help. Problems like yours allow me to test my skills using Monarch.  I fugred that the spacing issue could be a problem. I wasn't sure if I got the message across. I am glad to see, that you were able to get it working for you.

                             

                            Nick,

                             

                            Yeah, isn't that great? Who'd a thunk it? WHen I did it the first time, I was sure that I was either going to get an error message, crash Monarch, or fill up both columns at the same time.

                            • Record Filtering
                              Lynette _

                              Hello and Season's Greetings,

                               

                              Am a 7.2 pro user and what i suggest below may be available in 8 so please excuse, but did not want to miss an opportunity to state what I would like in addition to the those nice features recommended above.

                               

                              - Crosstab summaries.

                              ____please ignore this message....