4 Replies Latest reply: May 15, 2014 9:58 AM by GreerStopNut _ RSS

    Filter AFTER Summary

    GreerStopNut _

      I’m new at this so please forgive my ignorance.  Using Monarch Pro v. 5.02, I’m trying to get a simple list of available inventory.  I’ve found a way to get a list of stock availability whether positive or negative, but I can’t get Monarch to filter out the negative stock AFTER the summary has summed. I guess that would be the easy question/answer:


      Is there a way to filter the summary table after it has summed up the fields?


      Perhaps the harder question/answer would be:


      Is there another way I can pull the needed information from the report, so that I can get the final information I need?


      Here is the report. crossing my fingers hoping the spacing comes out right Let me know if it doesn't and I'll send a copy directly.


      [font="courier"] DM-D-020       OWNER CODE N           ACCUMULATED AVAILABILITY ANALYSIS                             15JUL03










      --- -


      N501375                                       3          541            10JUL03 H4  135306-002-003 GE SUPPLY LOGISTICS

      N506095     F52N7582   0820

                       8,273                                  8,273

      N506110     F12N7699   0820

                         777                                    777

                                                 3,000        2,223-           01SEP03 H1Y 132657-002-002 I.L.S. - COLUMBIA

                                     6,204                    3,981  S 05MAY03             ORDER: 119545     LOT:

      N506120      59N7856   0820

                      33,549                                 33,549

                                                23,000       10,549            06AUG03 OA  133896-001-001 EMD/GMC WHSE 529

      N506150     F51ND7528  0820

                       8,269                                  8,269

                                       100                    8,369  S 05MAY03             ORDER: 119545     LOT

      N501735       80HS080  0428

                           0                                      0

                                    10,259                  -10,259  S 23JUN03            ORDER: 119432     LOT:

                                                10,559          300            01SEP03 H1Y 132657-002-002 I.L.S. - COLUMBIA[/font][/quote]After the same header info on every page, it lists a part number for each part we have stock on and orders and production in process if any.  The number immediately below the part number (first indention) is the amount of that part currently in stock. The lines with “ORDER” in them are projected production runs of the part (projected quantities at the second indention.)  The other lines are customer orders (order quantities at the third indention.) The middle column is a running tally of available parts.


      I need a list of


      A. all our part numbers that have stock available after orders due within 12 weeks have been subtracted from current available stock, not taking into consideration any production runs (We don’t know for sure when or how many will be completed) and

      B. that new amount of actual available stock.


      I’ve tried multiple line fields but between the production run lines and header getting in the way I can’t get it to work.  This is the closest I’ve been able to come:


      I make a detail line that highlights the locations for current stock quantity , the order quantity , and the order due date on every line. (getting lot’s of “Nulls”)


      I make an append line highlighting the part number .


      I make a calculated field to find how far out the due dates are.



      I filter out the ones more than 84 days (12 weeks) away, keeping the “Nulls.”

      (isnull().or.[Days Out]<84)


      I make another calculated field subtracting the orders column from the stock column.


      This column doesn’t make sense until the summary table.


      I make a summary table using as the key field and as the summary field.


      Monarch nicely lists each part number once with the total positive or negative available stock. Now I want to filter out the negative fields.  But when I apply a filter to the summary it takes out the negatives and THEN sums.  I want it to sum and THEN take out the negatives.


      So is there a way to do that?  Or is there a better/different way to take the information out of the report that will leave me in a better position to get the final answer I’m looking for?


      Thanks for any help!




      [size="1"][ July 16, 2003, 10:52 AM: Message edited by: GreerStopNut ][/size]

        • Filter AFTER Summary
          Nick Osdale-Popa

          As a suggestion, if you use the [/code] formatting options around your report design, it  will make a better representation of the data. It's not perfect, but at least it doesn't wrap the lines the way that the HTML does.

          • Filter AFTER Summary
            Grant Perkins



            Just to confirm where you are at.


            It sounds like you can get everything you need out of the report upto a summary point one step BEFORE the final list you need. And you are happy with what you get up to that point.


            Is that correct?


            If so one simple solution would be to export the summary to a file (or report, but you have 5 Pro so a file would be OK. Make it comma delimited perhaps though you can choose any available formats.)


            Then open that file as a database and define the filter you wish to use in a model related to the database.


            That would be the simplest and most foolproof solution for the sort of situation you describe, if I have understood your needs correctly.


            An extension of that idea would be to linked database with the original report (pass through the report a second time in effect) to add a field from the 'database' (e.g. the qty value from the summary) and then use that as a filter to take out the records that will end up with negative values.


            You may also be able to do that via a clever trick with a summary by copying the summary and pasting it back into a calculated lookup field. Does much the same thing as using the database but would work for Standard edition users who lack the Pro version functionality to connect to an external file.


            See  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000166#000008"]here[/url] for a description of the process for a similar requirement as posted by Winn.


            On the other hand you might be able to perform the calculation you are leaving to the summary BEFORE you get to the point of summarisation. If you can do that you can filter on the field BEFORE getting to the summary - but then you may not need the summary at all!


            That said I will look to make some time to play with your sample data and see what other ideas come out of it. Any chance of a re-post of the data as suggested by Nick? The current format requires a lot of guesswork. You don't have to re-post as it should be possible to edit your original post and insert the  "CODE  /CODE"  commands (refer to the 'Instant UBB Code' section under then edit box) around your previous post.



            Alternatively if you can release a copy of of an original file (or part of one)I would be quite happy to play with that. Let me know and I will provide an email address either via mail or the Private Message facility.


            Best regards,




            [size="1"][ May 19, 2006, 12:01 PM: Message edited by: Todd Niemi ][/size]

            • Filter AFTER Summary
              Steve Caiels

              Hi Jesse,


              I'm not sure if I understand your exact requirements, but is it something along the lines of turning

              [font="courier"]Type             Qty

              CD                29

              DCC              -14

              LP                 0

              MD                 7

              TAPE             -12

              Summary           10[/font][/quote]into

              [font="courier"]Type             Qty

              CD                29

              LP                 0

              MD                 7

              All Others       -26

              Summary           10[/font][/quote]If so, double click on the first key field (Type} in your summary, go to the matching tab and select "field" qty is at least 0.


              It is not possible to remove the "all others" or summary lines in V5, I'm afraid.  In V7, you can remove the Summary line, but not the All Others line.





              • Filter AFTER Summary
                GreerStopNut _

                Ok I did the code/code thing and it looks pefect now.


                I know I could (and have) export the summary and refilter it. But I was certain there had to be a simpler way.


                Steve's suggestion is the right idea.  I don't want the "summary" or the "all others" lines, but it will work. It's not quite worth upgrading (not that that's even up to me) to exclude one line.


                Still, anyone with lot's of spare time on their hands, feel free to figure a way to pull the info differently or fiddle with it so that I can make the calulations in table view or whatever to keep from getting the "all others" line.  The easiest way that comes to mind is to get all the info on one record and put a calculated field at the end, but I can't figure out a way to do that.


                Thanks for the help!