4 Replies Latest reply: May 15, 2014 9:53 AM by Steve Caiels RSS

    Sort Summary by Calculated Field

    debijo _

      I'm having trouble with a sort on a summary.  I have 3 key fields:

      date, hour, minute

      I have 1 calculated field:

      average processor utilitzition

      I want it to sort in decending order by average processor utilization. 

      It doesn't seem to work the way I want.  I have changed the sort of each of the key fields to be decending by calcuated field, but if I look down the calculated field, the largest are not all at the top.  It still seems to be sorting inside each key field (like all of 1 date is on top, then the next date, etc.).  I tried making some item fields, but that didn't work either.  Is there any way in Monarch to do this?

       

      Thanks in advance,

      Debi

      V8.0

        • Sort Summary by Calculated Field
          Mike Urbonas

          How is your Summary aggregating your "average processor utilization" field?  Go to your Summary Definition (Edit -> Summaries -> Edit...-> Fields) and check the calculation Monarch is doing in the summary on your "average processor utilization" field.

           

          It may be that your summary is adding up (SUM) every instance of your "average processor utilization" from your Monarch table.  You probably do not want your summary to do any aggregation of your "average processor utilization" field.  If this is the case, try to change the summary calculation from SUM to MAX.

           

          Mike

          • Sort Summary by Calculated Field
            debijo _

            The field is actually the processor utilization for every 45 seconds...  The calcuated summary field is AVG of it.  That is what I want.  But, irregardless, it will not sort by this calculated field in descending order.  Any idea why?  In the meantime, I had to copy all the summary data to excel and sort it there.  Just a step I'd rather not have to do everytime.

            Thanks again,

            Debi

            • Sort Summary by Calculated Field
              Grant Perkins

              Originally posted by debijo:

              The field is actually the processor utilization for every 45 seconds...  The calcuated summary field is AVG of it.  That is what I want.  But, irregardless, it will not sort by this calculated field in descending order.  Any idea why?  In the meantime, I had to copy all the summary data to excel and sort it there.  Just a step I'd rather not have to do everytime.

              Thanks again,

              Debi /b[/quote]Debi,

               

              If I am reading this correctly you are selecting some key fields, adding them to a summary and then making a calculated fields an AVG calculation. Is that about right?

               

              The key fields will be sorted by default in a summary I would have thought. If you have multiple key field combinations with different AVG results then the sort would come into play - but that seems unlikely given the calc is an average anyway.

               

              Excel will do it simply because it is just seeing rows of data, not field set up in a summary as key fields.

               

              Monarch could also do it if you exported the summary and read it back in as a report, mapped the fields and then sorted as you wished.

               

              Or have I completely missed something in the information provided? (Quite possible I will admit.       )

               

              Grant

               

              Edit to try to add additional clarity.

               

              Using a summary with 2 key fields - a year and an ID number and sorting by the SUM(count)ascending since AVG is not applicable to my data - the year with the overall smallest SUM of count is always at the top of the list. If I dril up to that level the output looks OK. If I drill down to the next field the year (now multiple rows) is still at the top of the list but the SUM(count) figures look random. I can now sort the second key field WITHIN THE FIRST KEY FIELD POSITIONS by the SUM(count) but this will not change the order of the first key field positions.

               

              Does this make sense?

               

              [size="1"][ June 28, 2005, 04:51 PM: Message edited by: Grant Perkins ][/size]

              • Sort Summary by Calculated Field
                Steve Caiels

                Hi Debi,

                 

                I think you are seeing the standard behaviour in Monarch.  As you say, it will sort within each key field.  So the DATE with the largest value will be at the top, inside that it will be the hour with the largest value, followed by the minute.

                 

                As you are sorting, I assume you do not want to sub total by date and hour, so you should be able solve this by creating a combined datehourminute filed in the table and having it as a single key in your summary.

                 

                If they are characters, the calculation will be just date” “hour” “minute.  If they are numeric, then try  str(date,10,0)” ”str(hour,2,0)” “+str(minute,2,0)  

                 

                The 2nd parameter is the length of the string, so you may need to adjust that to match your formatting in the table.

                 

                Regards

                Steve