2 Replies Latest reply: May 15, 2014 9:53 AM by mclark _ RSS

    HELP! Breaking summaries down by count

    mclark _

      Hello Monarch Smart People,

      This is a time when you wish you could see summary data in the table window so you can do calc fields and filters on summarized data but anyway here goes.


      In one pass, I would like to be able to group values based on the count. So I have docs with the # of pts they've admitted.

      Doc A  500

      Doc B  400

      Doc C  250

      Doc D  155

      Doc E  100

      Doc F   90


      If I want the groups to be >300,299-200,199-100, I can do the >300 by using the at least feature on the measure in the matching tab BUT I can't use this for 299-200 group because I will get everything above or if I use the at most feature I will get extra docs below that I don't want. Any suggestions other than exporting the counts out and import into a new model? That would be great! Thanks, Michele

        • HELP! Breaking summaries down by count
          Grant Perkins

          Originally posted by mclark:

            Any suggestions other than exporting the counts out and import into a new model? That would be great! [/b][/quote]Hi Michelle


          If I recall correctly Nick Osdale-Popa is probably most knowledgable about this. I seem to recall he has been working through a similar requirement.


          The nature of the problem - using measures as temporary data - doea mean that the new dataset has to exist in its own right somewhere somehow not just as a (potentially) continuously variable calculation field or two. Whether the functionality performing the further analysis requires, as Monarch does, a multi-step process from the process developer's point of view or is hiding the nuts and bolts functionality in what appears to be an integrated activity is the question.


          You have the option of using colour formatting on the summaries according to the cell values. It's not what you seek but may be useful in some way if visual 'grouping' is all that is required.


          Alternatively, as a sort of half way house, you could export the results of the summary you have  to be used as an external lookup table reading back into the same model. From the lookup value you can assign a group "label" using a calculated field. The label can be used as a key field in a second summary, in the same model, to provide the grouping analysis you require.


          If all of this is set up as a project with project export and run through a batch file (or equivalent program) it should be possible to both ensure that the lookup values are the correct ones for the current data table at its current filter level and make it reasonably seemless as far as the user is concerned.


          Does this suggestion help at all?





          PS: You can also consider using an internal lookup table and making the user do the work. The summary would then just need


          Doc    Count


          Copy the 2 columns and then paste the results into an "Internal lookup" calculated field and then use the count value for each Doc to populated the grouping code as for the external lookup. However this relies on the user to do the work and cannot be automated using a simple batch file as far as I know.

          • HELP! Breaking summaries down by count
            mclark _

            Ahhh yes. Thanks for the helpful alternatives. Probably the labels will work best. Great!