13 Replies Latest reply: May 15, 2014 10:04 AM by swheat _ RSS

    Filter or Summary?

    swheat _

      I'm not sure if this question fits under summaries or filters, but in my search on the forums I couldn't find a similar situation so I decided to ask the question.

       

      Identical records in the table need to be netted together, even if the amounts are different.  All other fields should be identical.  I assume I would need to create a summary to do this, and actually I have a summary that performs this very well.

       

      After the records are netted together I need to filter out any positive amounts leaving only the negative amounts.  I tried creating a filter and including it in the summary, but it only seems to work on each individual record instead of on the summed records.

       

      Am I missing a step?  Or will I have to do this with two models (I read about that in a solution for something else on the forum)?  Obviously it would be helpful if it could be done in one model, but I haven't been able to find the solution yet.

       

      I appreciate any tips you can give me.  By the way, I use Monarch Pro V.

      9.0.

       

      Thanks.

        • Filter or Summary?
          Data Kruncher

          You're correct: you will need two models to do this.

           

          First setup the initial summary without displaying any subtotals. This will get you a Table-like presentation with both positive and negative values, and possibly even some zero values.

           

          Export this summary to your format of choice. Now in a separate Monarch session, open that export as a database source.

           

          At the initial filtering opportunity, setup a filter that excludes all values >= zero.

           

          This will leave you with the items that roll up to negative values.

           

          If this is a regularly occurring process, you may want to save both models and project files, with the first model having a defined project export. Then you can create a short batch file to execute the first export and then run the second database-type project.

           

          HTH,

          Kruncher

            • Filter or Summary?
              Gareth Horton

              It's a testament to Sandy's abilities that I am almost unsure that I have understood this correctly!

               

              It is possible to do this in one pass by using the matching functionality, if I have understood correctly.

               

              In the first summary key field you are using, go to the matching tab and select Measure limit (choosing your net amount measure) >=0.

               

              This should do the trick, listing only the records that have a summarized measure value of 0 and above.

               

              You can then use the Unmatched values options to hide the All Others aggregation if you like, but remember that any Grand Totals will include those values.  You can hide any Grand Total lines in the summary properties.

               

              Gareth

               

              You're correct: you will need two models to do this.

               

              First setup the initial summary without displaying any subtotals. This will get you a Table-like presentation with both positive and negative values, and possibly even some zero values.

               

              Export this summary to your format of choice. Now in a separate Monarch session, open that export as a database source.

               

              At the initial filtering opportunity, setup a filter that excludes all values >= zero.

               

              This will leave you with the items that roll up to negative values.

               

              If this is a regularly occurring process, you may want to save both models and project files, with the first model having a defined project export. Then you can create a short batch file to execute the first export and then run the second database-type project.

               

              HTH,

              Kruncher[/QUOTE]

                • Filter or Summary?
                  swheat _

                  Thank you both for the suggestions...I'll give them a try and let you know which one worked for me.

                    • Filter or Summary?
                      swheat _

                      I tried Gareth's suggestion for using the Measure limit.  However, I can't find the Unmatched values option to hide the All Other aggregation.  Is that a feature in 9.0 or something later?  I have 9.0.

                        • Filter or Summary?
                          swheat _

                          Even without being able to hide the All Other aggregation, I am finding that using the Measure limit it not completely returning the values I need.  It comes very close, and I would love to get it to work so that I only have to use one model.

                           

                          The other issue I'm running into is that if there are duplicate records in the key field, then it aggregates the measure limit value and sometimes leaves positive numbers when I'm only looking for negative.  See the sample below

                           

                          [FONT="Courier New"][SIZE="4"]Inv No     CO   Cust No     Customer Name               Type     Pol No          Co No     Tran Date   Eff Date    Cust Total

                          72037     0734 JMBACKHOES     J & M Backhoe Services, Inc     CUM     79834325DAL     CHU01     2009-05-19  2009-03-11  -4137.00

                          72037     0734 JMBACKHOES     J & M Backhoe Services, Inc     CUM     79834325DAL     CHU01     2009-05-20  2009-03-11   4137.00[/SIZE][/FONT]

                           

                          As you can see the records are almost identical, except the Tran Date is different, so my expectation is that the second record would be eliminated from this Measure limit.  What am I missing?

                            • Filter or Summary?
                              swheat _

                              Data Kruncher - I have been looking at your suggestion and I have some questions.  Is it possible to put more than one model in a project?  If not, can the batch file you suggested run both projects?  I have created batch files for a single project, but have never tried it for more than one.  Are there any special tips to do that?

                                • Filter or Summary?
                                  Data Kruncher

                                  No, a project cannot use multiple models.

                                   

                                  A batch file can run as many processes as necessary to get the job done.

                                   

                                  The one thing that I like to add a regular, single Monarch task batch file, is a START command with a /WAIT parameter. This will wait until the Monarch session is complete until the next begins. START also require a title for proper syntax.

                                   

                                  The typical multiple pass Monarch batch file goes like this:

                                  START "Proj1" /WAIT "C:\Folder\Monarch.exe /prj:"C:\MyFolder\Project1.xprj" /pxall

                                  START "Proj2" /WAIT "C:\Folder\Monarch.exe /prj:"C:\MyFolder\Project2.xprj" /pxall

                                  "C:\Folder\Monarch.exe /prj:"C:\MyFolder\Project3.xprj" /pxall[/code]

                                   

                                  You don't need the START bit for the last project in the batch.

                                    • Filter or Summary?
                                      swheat _

                                      Thank you...I'll give it a try.

                                        • Filter or Summary?
                                          swheat _

                                          I finally got a chance to work more on this issue, and I almost have the multiple pass batch file working.  However, I keep getting an error message on the first project.  It says, "Monarch application file has encountered a problem and needs to close.  We are sorry for the inconvenience."  And then it has the buttons to send or not send the error report to Microsoft.  After I click on "Don't Send," it goes onto the next project and works just fine.  In the .bat file I have, I get the error message on the 1st, 3rd and 5th projects.

                                           

                                          I put "rem" on each of the lines and only ran the .bat file for the first project (after removing the "start" and "wait" commands), and I still got the error message.

                                           

                                          I am attempting to put the code below (I know there are specific intstructions for how to do this, but I can't remember how ).

                                          "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"G:\Accounting\Exec\Monarch\Projects\AR Credits\HOU AR Credits A.xprj" /mod:"G:\Accounting\Exec\Monarch\Models\Credit AR Balances Models\TX AR Credits A.xmod"  /S  /exp:"G:\Accounting\GL Team\z - Requests\AR Credits Process\Intermediate Export\HOU AR Detail.xls" /expfileopt: overwrite /px:"HOU AR Detail" /QUOTE

                                           

                                          Please let me know if you need any additional information to help troubleshoot the error problem.