3 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    apply a filter based on the result from a summary

    Sun _

      Hi,

       

      I have a report like this:

       

      AcctID Balance

      1111 $200

      2222 $400

      3333 $500

      4444 $600

      5555 $100

      6666 $700

      7777 $800

       

      I need to calculate the standard deviation of Balance which is $256.35 in this case. Then I want to export those record has a balance greater than the standard deviation:

       

      AcctID Balance

      2222 $400

      3333 $500

      4444 $600

      6666 $700

      7777 $800

       

      Grant, is there a way to let Monarch calculate the standard deviation and use the result as a filter without manually enter it in the filter?

       

      Thanks.

       

      Sun

        • apply a filter based on the result from a summary
          Grant Perkins

          Originally posted by Sun:

          Grant, is there a way to let Monarch calculate the standard deviation and use the result as a filter without manually enter it in the filter?

           

          Thanks.

           

          Sun /b[/quote]Hi Sun,

           

          I am assuming you Have a PRO version of Monarch, hopefully V8.

           

          I think your best option here is to firstly create a summary that gives you the standard deviation figure you need. This will appear in a SUMMARY line but you have the opportunity to change the text from SUMMARY to something else. I will use SUMMARY in this explanation.

           

          Export the summary to a file to be used as an EXTERNAL LOOKUP. A .csv or .tab file would be fine. Excel if you wish.

           

          You only need that SUMMARY value but all the summary lines will be exported.

           

          Back in your main model create a field to be used to link to the lookup file. The link data will be the text you used for the summary - so SUMMARY in this case.

           

          I used a RUNTIME PARAMETER field, gave it a value of "SUMMARY", ticked to store that in the registry between runs and then set the field to hidden.

           

          Now define the External Lookup. The file you need to link to is the one just exported and the link will be via the runtime field. So long and the text in the runtime field value matches the text you used for the summary line in the export - SUMMARY in my example - you will get a link field populated with the deviation value for all records.

           

          The final part of the process will be a filter in the table that comapres the $ value with the deviation value and filters accordingly.

           

          Althouh this is a two step process it can be self contained within a single model and automated using a script file if required.

           

          That's the outline of the process. Easier to do than to describe. If it does not make sense let me know and I will have another go!

           

          You may need to adapt this idea in some way for your specific purposes.

           

          HTH.

           

          Grant

          • apply a filter based on the result from a summary
            Sun _

            Grant,

             

            Thanks for your prompt response. Unfortunately I don't have Pro. I'm using Monarch V8 standard.

             

            Thanks.

             

            Sun

            • apply a filter based on the result from a summary
              Grant Perkins

              Originally posted by Sun:

                Unfortunately I don't have Pro. /b[/quote]Ah!

               

              OK. A similar idea is possible using an internal lookup table but you would need to run the model with an empty table to get the Variance value, enter the value in the table and then allow Monarch to update and apply the filter.

               

              In which case it is probably easier to skip the lookup table idea, since you have just the one value to use, and simply use the Runtime Parameter field to enter the value of the Variance for comparison.

               

              If you define the field so that a value is not required and not stored in the registry between runs on first running the model will be able to calculate the variance in the summary.

               

              Note that figure. (Or copy it to the clipboard.)

               

              Then either re-run the Monarch session and enter the value when prompted or, more usefully, use the Edit menu to access the Runtime Parameter field to enter the value (paste it if previously copied to the clipboard) and Monarch will re-calculate accordingly.

               

              Does that offer a workable solution for you?

               

              Grant