5 Replies Latest reply on Jun 9, 2010 8:57 AM by comment_1

    return zero in sum calculation

    otto_m

      Title

      return zero in sum calculation

      Post

      Thanks to SuperContributor "comment" for this valuable hint my report:

       

      Sum ( 
      1 ;
      Score ≥ 22 ;
      Score ≥ 43 ;
      Score ≥ 85 ;
      Score ≥ 153
      )

      It works well and ranks the number of applicants in levels 1-5 according to their score.

       

      There is just one little thing: FM omits the fields that are zero, often resulting in columns of three or four rows only.

       

      For comparisons, it would be good to have FM 11 return zero for levels without appropriate scores, consistently resulting in five rows.

       

      How can I accomplish this?

       

      Thanks in advance for your help.

       

      Otto

        • 1. Re: return zero in sum calculation
          ninja

          Howdy otto_m,

           

          It sounds like you have a check in the calculation dialog for "do not evaluate if all referenced fields are empty".

          Since you are only referencing one field, if it is empty, you would get a blank since it didn't evaluate the expression.

           

          Not sure how your Sum( ) function could ever return a zero or a blank, but if you uncheck that box, it will return a 1 when the field "Score" is empty.

           

          If you want it to be a zero if "Score" is empty, simply wrap your expression in a case statement.

           

          Case ( IsEmpty (Score) ; "0";

          Sum (

          1 ;
          Score ≥ 22 ;
          Score ≥ 43 ;
          Score ≥ 85 ;
          Score ≥ 153
          ))

           

          I'm interested in 'comment's comment on this one... ;)



          • 2. Re: return zero in sum calculation
            philmodjunk

            Does the score field store the value 0 in this case or is it actually empty? It sounds like your score field is empty. If that's the issue, clear the "Do not evaluate if all referenced fields are empty" check box in the calculation that you have posted.

            • 3. Re: return zero in sum calculation
              comment_1

               


              otto_m wrote:

              For comparisons, it would be good to have FM 11 return zero for levels without appropriate scores, consistently resulting in five rows.


               

              If I understand the request correctly, I'd advise you to consider very carefully if you really need this - because it's not going to be simple. Basically, you'd have to return to what you had before, in addition to what you have now (since I assume you still want the pie charts).

               

              BTW, it's best to keep an issue in the same thread in order to avoid confusion.


              • 4. Re: return zero in sum calculation
                otto_m

                Hi all,

                 

                Thank you for your comments, and sorry for opening a new thread. (I am new to this forum, and thought nobody would read the old thread once I accepted it as a solution.)

                 

                Here's some more explanations:

                 

                The "Score" refers to a candidate's actual score in the placement test (0-256 points), and this score is used to rank students in five levels in various degree programmes.

                 

                E.g. the scores of candidates for Degree Programme 1 range from a 32 to 140, so there's no level 1 nor level 5.

                        the scores of candidates for Degree Programme 2 range from a 12 to 128, so there's level 1 but no level 5.

                 

                When using the Sum calculation, FM returns the correct numbers for levels 2, 3, and 4. but it omits 1 and 5.

                 

                This makes visual comparison difficult because FileMaker's colour scheme of the pie charts is inconsistent:

                In Degree Programme 2, which has three candidates at level 1, the chart shows the same red wedge for level 1 which it uses for level 2 in Degree Programme 1.

                 

                • I tried unchecking "Do not evaluate if all referenced fields are empty",

                • and also tried modifying the calculation as Ninja suggested.

                 

                However, FM still returns all values without zero. If there is no simple method of getting the Sum calculation to do the trick, is there another way around the problem?

                 

                Thanks,

                 

                Otto

                • 5. Re: return zero in sum calculation
                  comment_1

                  I am afraid you are confusing two unrelated issues here: the "Sum calculation" does not really sum anything, nor does it omit anything - all it does is translate an individual score into a rank between 1 and 5 and that's it.

                   

                  The problem you are describing occurs when there are no candidates with rank 1 or 5. Filemaker does not summarize non-existing records. If you want to have a 0 sub-summary value, you will need a calculation field for each rank (returning either 1 or 0, depending on the candidate's rank), and 5 summary fields to sum up those cals.

                  And I am not at all sure how one would get those sub-summary values into the chart.