7 Replies Latest reply: May 15, 2014 10:01 AM by fino _ RSS

    Summary Expression -0.00

    fino _

      Hi,

      I have an unusual issue which is as follows:

      I have ~3500 records in TABLE window which have either Debit or Credit Amounts.

      When I create a SUMMARY Tab the number of Distinct GL accounts is 48 in total.

      When I sum the DR and CR and create expresssion SUM(DR AMT) - SUM(CR AMT) by distinct GL account I find that 3 of these GL accounts have -0.000000000 for above expression.

      I deliberately have 9 decimals in order to determine hidden decimal places, obviously to no avail.

      Why are there minus figures?

      regards

      Fino

        • Summary Expression -0.00
          Grant Perkins

          Have you tried setting up the summary to filter just to the problem accounts and then to drill down to the detailed transactions?

           

          My initial thoughts would involve checking that I was not somehow suppressing the display of the most significant digits ans possibly looking for value anomalies - nulls (unlikely I would think in this case) or some other aspect of the extraction that produces a strange result. I would certainly be viewing the detail of the records that make up the problem accounts as an early step. Look at the account with the smallest number of transactions to start with.

           

          If that produces nothing directly it may suggest the next thing to check.

           

          HTH.

           

           

          Grant

            • Summary Expression -0.00
              fino _

              Grant,

              thank you for your suggestion - however I have encountered this same issue again with another dataset. This time the # of records is 3!

              2 Debits of 799.34 & 21.70 and 1 CR of 821.64

              These exactly balance and still the formula

              SUM(CR)-SUM(DR) produces -0.00

              I am going to set uop dummy data files with exactly these 3 records in excel and then text files and see if I can figure out this problem.

              There is definitely something underlying causing this problem and will let you know when I find out what!

               

              cheers

              fino

                • Summary Expression -0.00
                  Grant Perkins

                   

                  2 Debits of 799.34 & 21.70 and 1 CR of 821.64

                  These exactly balance and still the formula

                  SUM(CR)-SUM(DR) produces -0.00

                  /quote

                   

                  My arithmetic tells me the above leaves a CR balance of 0.60. 

                   

                  Or -0.60 in some forms of financial report.

                   

                  I assume a typo in the post?

                   

                   

                  Grant

                    • Summary Expression -0.00
                      fino _

                      Correct - tpyo

                        • Summary Expression -0.00
                          Grant Perkins

                          fino,

                           

                          It's not obviously reproducible and sounds like it may be intermittent from what you originally reported.

                           

                          I'm not sure what to suggest to be honest. I assume that others have not seen the same problem (or have not yet seen the thread ...)

                           

                          I can't see that it would matter but, for completeness, is the data source a report file (i.e. text of some sort) or a 'database'?

                           

                           

                          Grant

                            • Summary Expression -0.00
                              Data Kruncher

                              While, like Grant, I wasn't able to reproduce the problem, I do have a suggestion that may[/B] solve the issue.

                               

                              Instead of using the numeric fields directly in the summary, first create two calculated fields in the table, one for each of the DR and CR amounts.

                               

                              DRCalc would be:

                              Round(DRAmt,2)[/CODE]

                               

                              and the matching CRCalc:

                              Round(CRAmt,2)[/CODE]

                               

                              Using the Sum(DRCalc)-Sum(CRCalc) should get you a regular zero amount.

                               

                              Should[/B], that is.

                                • Summary Expression -0.00
                                  fino _

                                  I have found something - I dont know why or how this happens but perhaps maybe somebody enlighten me.

                                  When I double click expresssion

                                  SUM(round(CR),2)-SUM(round(dr,2)) in summary view and change the

                                  Display settings Custom Radio button selection from "General" to "Thousands" all -0.00 values return to 0.00 which is correct - so the next question is why?

                                  I do not loose any decimal places for any of the other expression values.

                                  regards

                                  fino