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

    Summary Calcs and rounding/Truncate Options

    sxschech _

      Hi we are using monarch pro 8.01 and running some summaries to match the bottom line numbers off the report.  The report's numbers are truncated.  For example 52.5 whereas monarch would show 52.6.  If we view to three decimals then we can see 52.596.  Can monarch's display be adjusted to show 52.596 as 52.5?

       

      I learned how to do subtotals going down

            grp1  grp2  st

      Calif 500   100   600

      Orego 250   100   350

      Subto 750   200   950

       

      Is there an option to subtotal across?

       

      Can monarch do percentiles? (mean, 25th, 75th).  Presently, I am copying and pasting in excel to obtain the percentiles (using quartile function).

       

      Thanks,

       

      Steve

        • Summary Calcs and rounding/Truncate Options
          Grant Perkins

          Hi,

           

          If you want to simulate the trunction of the original report you would, I think,  need to treat the 3 decimal place field as a CHARACTER field, lose the last 2 characters and then use the result, converting back to numeric if necessary.

           

          One approach would be to create a calculated field. STRing the number to characters then INSTR the result (probably with LTRIM to remove leading spaces) the numeric position of the decimal point. Add one to that number and the LEFT the character string for the resulting number and you should have the turncation you require.

           

          There are several posible variations on this theme.

           

          The subtotals question sound like something that a summary should provide automatically unless it has been changed to do something else. Have I missed something in the question?

           

          I think percentiles may be something that is beyond the scope of Monarch's design objectives but it is not withing my area of expertise to comment much. Other than to say that if the Help section relating to Aggregations in the summaries does not provide the information you hope to find then I suspect Excel is still your best option.

           

          You might want to consider whether cut and paste or exporting are your best options for getting the data into Excel but that's about it.

           

          HTH.

           

           

          Grant

          • Summary Calcs and rounding/Truncate Options
            sxschech _

            Grant,

             

            I'll try the string options you discussed for truncation.  As for totals, the summary shows totals, but I would like to have the summary show a total of the totals.  If the summary has 2 cols, then I would like to display a third col that would add col1 + col2.  Since Col one and Col two are Averages, is there something special that needs to be set/done to get the sum of these averages to display on the summary level?

            • Summary Calcs and rounding/Truncate Options
              Grant Perkins

              A couple of observations.

               

              If the values you need to truncate are the result of the summary calculations, as I suspect they are having re-read your posts, then the conversion to character and truncation will not work afaik unless you export the summary with values to 3 decimal places and then apply a new model to that and trunacate. That said I hope the numbers that are being truncated rather than rounded are not important for any sort of audit process.

               

              There may be some sort of formula that could be applied to the summary calc to simulate the truncation but I can't be sure.

               

              Secondly the existence of a row total in a summary is dependent on the measure being totalled relating to a single measure.

               

              So for example if you had an order report analysis by customer with measures of order line qty and order line value and set up a simple summary with Customer Number as the key and the two measures you would see both measures and get a measure summary at the bottom of the column and no customer number summary as the two measures are not related to each other.

               

              If you added another key to the summary - say product code for example, and displayed that ACROSS the summary would offer a single measure for display and then provide the linear totals (or average for all products if you set the measure to be an average.)

               

              I may have missed something in what you are doing but but I'm not exactly sure what a sum of two averages of, presumably, different measures or 'things' is really telling you anyway.

               

              So, taking your original table, if GRP1 and GRP2 are two values of the same field (Field is GRP, Value of the field can be 1 or 2, etc.) then you can set the summary display to be ACROSS (using the summary field propeorties dialogue) and see the line subtotals for the measure selected but with the linear subtotal representing a calculated AVERAGE (not a sum) if the measure in use is an AVERAGE.

               

              Does this help at all?

               

              Grant