7 Replies Latest reply on Jan 23, 2015 4:05 PM by philmodjunk

    If statement with Summary results seems wrong

    Stu412

      Title

      If statement with Summary results seems wrong

      Post

      Hi there

      I have 4 summary fields on a table calculating various numbers which when displayed on the sub summary row are correct.

      I want to create another column on the same row which displays only one of the summary fields given a certain condition.  I have tested the If statement in the calculation field and the logic around the condition is also correct.

      So, I think to myself, hey, replace the test "This is a test" comment with the Summary and away we go....

      Not so, the result I'm getting from the "If Summary" (for lack of a better term), despite being the same formula in the same table is completely different to the same summary result in the column right next door!

      Any ideas why a summary would change results when it's used in the results of an If?

      Thanks

        • 1. Re: If statement with Summary results seems wrong
          philmodjunk

          When you refer to a summary field inside a calculation, the field evaluates to compute a "grand total" type result just as though the summary field were located inside a footer, header or grand summary layout part. It does not return the "sub total" result that you see in a sub summary part.

          To access a subtotal inside a calculation, use the getSummary function and use the same "when sorted by field" specified for your sub summary part as the "break field" parameter in the getSummary function.

          • 2. Re: If statement with Summary results seems wrong
            Stu412

            Ok, I totally get the logic of that, it makes sense.  I can't grasp why my GetSummary is empty, yet the Summary for the same data (broken in the summary part by the same field) works fine...

            I have a part based on 'Year' and the summary is Summary(Fees).

            So I think....GetSummary(Fees;Year) should give me the same...yet it's just an empty cell.  Not even a question mark!

            FM is a twisted mistress indeed...

             

            • 3. Re: If statement with Summary results seems wrong
              philmodjunk

              Make sure that your found set is sorted by the Year field.

              For GetSummary, the Year field must be defined in the same table as Fees and both need to be defined in the same table that is the data source table for your layout.

              The Sub Summary layout part can use a "sorted by" field from another table, GetSummary cannot. This sometimes requires adding an extra calculation field that just copies the "sort" field into the same table as your summary field. (And then you sort on this calculation field.)

              • 4. Re: If statement with Summary results seems wrong
                Stu412

                Is Get Summary affected by multiple sort fields?  I'm sorting by customer and year, both of which are now on the source table (thanks for that!)

                It's still an empty result - almost like I need Get Summary (fees;Customer;Year)

                 

                • 5. Re: If statement with Summary results seems wrong
                  philmodjunk

                  You need to specify the same sort field as that specified for your sub summary layout part in order to get the same sub total.

                  Make sure that this field is defined as a calculation field. It can't be a number field with an auto-enter calculation using this expression.

                  • 6. Re: If statement with Summary results seems wrong
                    Stu412

                    Sorted, 

                    As you cannot use a summary in a calculation field, I've calculated the IF's on their own field and summarised that instead.

                    It seems to work...

                    Thanks Phil :)

                    • 7. Re: If statement with Summary results seems wrong
                      philmodjunk

                      But you can use a summary field in a calculation field. I do that all the time.

                      But if you are using IF to determine whether or not the value from that record should be part of the summary, then your approach is one of several that may be used to produce that result.