11 Replies Latest reply: Sep 2, 2016 5:49 AM by Frank Walka RSS

    Summary Rounding Issue

    Frank Walka

      Ok so I'm having trouble with Monarch rounding a figure and cant seem to determine how to correct it

      Beginning                  Adjust            Combined$            Ending

      ($264,297.47)    $11,151,347.03    $13,385,826.36     $24,272,876.00

       

      There is no ending data so I created an ending column in my table are hard coded it to 0.00. I added this is a measure in my summary and gave it this formula

      Ending Summary Measure Calculation

       

      sum([beginning]) + sum([adjust])+ sum([Combined$])

       

      As you can see above the results of this should be 24,272,875.92, however Datawatch is rounding for some reason..... I set the format and decimal count to match on all

       

      Please help if you can!!

       

      Thank yu

       

        • Re: Summary Rounding Issue
          Austin Perkins

          Frank,

           

          On your calculated field, did you remember to add decimal places to it as well? I would likely assume that you did based on your last sentence but thought I'd take a stab at the easiest possible solution!

           

          Austin

            • Re: Summary Rounding Issue
              Frank Walka

               

              So the ending column was added to my tale with a value hard coded as 0.00. To get that bottom row to net across I created a summary and inserted a totals line. Then I did the calc beginning + adjust + combined. Which should equal the ending balance. For some reason its rounding some figures and not others. Scrolling through the data it seems like its rounding everything over 250K or around there...so odd

                • Re: Summary Rounding Issue
                  Olly Bond

                  Hello Frank,

                   

                  Just to check, what version of Monarch are we dealing with here? Classic or Data Prep?

                   

                  And what's the source data format - a text report, a PDF, CSV, Excel or something else?

                   

                  Best wishes,

                   

                  Olly

                    • Re: Summary Rounding Issue
                      Frank Walka

                      This is monarch classic v13. The source data is a .CSV. Thank you for looking into this

                        • Re: Summary Rounding Issue
                          Olly Bond

                          Hello Frank,

                           

                          It sounds like odd behaviour - I can't replicate it using Betty's Music Store data. The numbers in your sample aren't in the trillions, so the limits of the numeric field format shouldn't be the cause of the problem. Just adding the cents in the last column of your summary suggests something is wrong - they don't add up to the total. What happens if you go back to the Table view and Rebuild Data View? And then save the model and close everything and open again? Is it consistently wrong?

                           

                          Best wishes,

                           

                          Olly

                          • Re: Summary Rounding Issue
                            Grant Perkins

                            Frank,

                             

                            Which release of V13?

                             

                            On a whim I tried the calculation in your original post in the calculator on my Android (Cyanogen Mod) phone. The Calculator display screen seems unable to show the last digit for come reason. That said I am using the original OS as delivered over 18months ago so there may be an update.

                             

                            The same thing using my Windows phone works correctly.

                             

                            Putting you numbers into an Excel spreadsheet, opening that in Data Prep and adding a calculated field to total  the 3 values also works correctly.

                             

                            Rather strange that it seems to affect some combinations and not others.

                             

                            Grant

                    • Re: Summary Rounding Issue
                      Frank Walka

                      I think I'm going to try rebuilding it from scratch since it seems like such an odd error. I wanted to make sure I wasn't missing some weird setting.

                      • Re: Summary Rounding Issue
                        Frank Walka

                        I built the entire project over and the problem persisted. I think it was something to do with performing the calculation in the summary fields. I created a separate field in the table view and did the calc across in my table and it worked fine.

                         

                        Thanks to everyone who tried to help.

                         

                        To me it looks like monarch gets a little buggy when you mess with the calculations on your measure items. In theory this should have been quite possible to get it to add across.

                          • Re: Summary Rounding Issue
                            Grant Perkins

                            Frank,

                             

                            Trawling back into the subconscious depths of my memory bank I think that all you are seeing is the likely small rounding errors than can accrue when doing floating point precision calculations on large numbers.and then rounding the results back to displayed values.

                             

                            This is a phenomenon much discussed in Excel centric sources.

                             

                            A summary in Monarch is, potentially, quite a complex thing since for speed or display and response when drilling up and down (for example) all possible values are calculated and then presented on demand rather than using a recalculation for each step when nothing else has changed.

                             

                            Where a lot of large numbers are generated the floating point precision may be less than for small numbers and so rounding and other factors may come into play and unexpected effects may creep in. All values are calculated and accumulated individually in the array, not just summed in the displayed columns or across subtotal rows.

                             

                            There may also be some interesting effects when rounding up and down if a mix of negative and positive numbers are involved.

                             

                            As I am no expert in these matters I will defer to the link that follows. I would guess it applies to Monarch and most other applications as well as Excel.

                             

                            See if this helps (MS about Excel) but you may like to do a little more research before considering the "Precision as displayed" option in Excel - see the note in the Method 2 section - when using Excel.

                             

                            https://support.microsoft.com/en-gb/kb/214118

                             

                            I hope this is of interest.

                             

                            Grant

                            • Re: Summary Rounding Issue
                              Grant Perkins

                              Frank,

                               

                              I am advised that this anomaly for summaries should be resolved in the Monarch Classic 13.5 release and my quick testing suggest that it is.

                               

                              It would be great if you could repeat your summary definition and see what results you get after the update.

                               

                               

                              Grant