9 Replies Latest reply: May 15, 2014 9:54 AM by Olly Bond RSS

    problem with summary

    rebecca _

      I am attempting to sum a numeric column and it keeps telling me the answer is 0.  I know that it is not.  The fields that I am attempting to sum are calculated numeric fields.  I did change some of the formulas.  I have tried deleting my old summaries and re-adding, but to no avail.  Any suggestions?

        • problem with summary
          Grant Perkins

          Rebecca,

           

          Are any of the values you are summing likely to have produced a NULL (or non-numeric) result from the formula?

           

          From memory I think this can give you the problem you describe - but it could be something else.

           

          Try some filters to see if you can get subsets of the data which do add up correctly - just one way to check what is going on.

           

          Grant

          • problem with summary
            rebecca _

            Yes some do have null values... is that the problem?  How do I work around it?

            • problem with summary
              rebecca _

              okay adding filters to eliminate nulls does not fix the problem 

              • problem with summary
                Grant Perkins

                Originally posted by rebecca:

                Yes some do have null values... is that the problem?  How do I work around it? /b[/quote]OK, you need to ensure that the values in the field you are calculating will not be null - i.e.e the formula will always give a numeric result. Null can be the result of a divide by zero error for example.

                 

                I think it will be useful to revisit the formula and ensure that all the fields it uses are themselves numeric and thet none of them can give a zero result - it all gets a bit formula specific at that point.

                 

                You may nee to put something into the formula that checks for NULL (eg using the ISNULL function to test the result) and then ensure that any nulls are converted to zero or something suitable like that.

                 

                My suggestion on filters was really intended for slicing the data (if possible) into smaller chinks to see if the formula worked for some groups of records and not others.

                 

                Sometimes drilling down in a summary can show that some keys calculate OK at the summary level and some do not.

                 

                How have you got your options set for the display of nulls? Are they obvious in the table - "null" or blank for example?

                 

                Grant

                • problem with summary
                  rebecca _

                  They were set to display as 0 and I am now changing to null.  I'll go back and look at my formulas.  Thanks.

                  • problem with summary
                    Grant Perkins

                    Originally posted by rebecca:

                    They were set to display as 0 and I am now changing to null.  I'll go back and look at my formulas.  Thanks. /b[/quote]Ok, that should make things visible for the purpose of what you are now doing so it will be easier to see what is going on.

                     

                    The resolution, assuming the data feeding the formula is as it needs to be  - ie the null calculation is in fact valid - is a little more difficult in some ways.

                     

                    For example, you could put a check into the feeding fields for the formula such that if the result is NULL make the field numeric zero or, perhaps, 1, depending upon what it is to be used for in the next calculation. That would make the calculation run BUT may give misleading results. That in turn might be a problem for audit and compliance requirements.

                     

                    I don't know the purpose of your calculation but take care to consider issues like that when working out what sort of fix is appropriate.

                     

                     

                    Grant

                     

                    Edit for typo.

                     

                    [size="1"][ June 16, 2005, 10:38 AM: Message edited by: Grant Perkins ][/size]

                    • problem with summary
                      rebecca _

                      Yep this was the problem.  I forced all nulls to zero and now my summary works great.  Thanks!

                      • problem with summary
                        Bruce _

                        I know i have formulae that result in nulls even though the formula does not have divison in it.

                         

                        My simple formal was A - B which resulted in null if either A or B are null. I had forgetten that I had the Monarch option "display nulls as zero", so I could not find the error for a while.

                         

                        Once I changed to

                         

                        if(isnull(A),0,A) - if (isnull(B), 0, B), the problem was solved.

                         

                          smile.gif[/img]

                          • problem with summary
                            Olly Bond

                            We've seen this a couple of times now, and thought I'd post to see how anyone else has solved this problem.

                             

                            In a table[/B], we can solve it by setting the Options>Input to treat null data as zero, so it doesn't cause problems later on. But summaries can be more tricky.

                             

                            Assume that we have a summary[/B] where some values are NULL. We want to display this data as a zero, so we set this in Options>View. If we then export this data to Excel, then the zeroes that represent the null values are treated as text and not as numeric cells in Excel. You can see this usually by their being aligned left with a small green triangle in the corner.

                             

                            I've seen ASAP Utilities (http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=70&utilities=Numbers[/url]) which looks like it could do the job, but it would be nice if Monarch had an option to view & export NULL as a numeric zero rather than as "0".

                             

                            Best wishes,

                             

                            Olly