11 Replies Latest reply on Apr 13, 2012 10:44 AM by disabled_JustinClose

    Calculation on summary fields not working

      Title

      Calculation on summary fields not working

      Post

      Hey all.  (Second darn time I have had to type this because of some odd browser navigation error that caused me to go back a page.  Then my edits are all gone...grrr....  And there is only one large attached image, a composite of all four mentioned below; they were supposed to be individual shots included inline.)


      I am having trouble with creating a summary calculation (difference) of other summary fields.   It works in some places, but not in others, depending on the sort.  This is part of a planning module; there are 'Estimated' hours and 'Assigned' hours for each 'Quarter' and 'Category'.  Each project has a target 'Quarter' for completion, is of a certain 'Type'.  As hours are assigned to projects, they want to see a summary totaling the estimated hours and assigned hours, as well as the difference between them.  Like this (these views are records filtered down to 1 Quarter, all Types) (see Diff_1A and Diff_1B of the attached image - couldn't seem to paste individual shots in the messages body).


      [images Diff_1A and Diff_1B]

      (You can ignore the 'Courses' summaries:  firstly, they are working fine, but mostly because there isn't a Difference calc going to be done for those.)  Here is the same layout, just sorted by Type then Qrtr (and you can see that now both calcs are wrong):

      So this view is sorted by Quarter first (black), then 'Type' (dark gray), then 'Category' (light gray).  You can see the difference field on the right, which is working fine in the Quarter SS row, but not the Type SS row.  (They are different fields, explained below.)


      However, in some cases each calculation appears to work fine!  If the record set is restricted/filtered to only one Type, the calculation works in either sorting method (see the bottom two caps).  The views in those two are filtered to 1 Quarter, 1 Type.

      [images Diff_2A and Diff_2B]


      Here is an attempt to demonstrate the fields involved (the 'Courses' summaries are left out):
      ---------------------------------------------------------------------------------------------
      SS-Quarter         [...]       Est_Q::Hrs_Sum    Projs::Total_Sum    Projs::Diff_Q
      ---------------------------------------------------------------------------------------------
      SS-Type             [...]       Est_T_Q::Hrs_Sum    Projs::Total_Sum    Projs::Diff_T
      ---------------------------------------------------------------------------------------------
      SS-Category
      ---------------------------------------------------------------------------------------------
      [body fields]
      ---------------------------------------------------------------------------------------------

      TOs and relations:
      EST is the base table for Est_Q (by quarter) and Est_T_Q (type and quarter)
      Est_Q  <->  Projs
          Qrtr = Qrtr
      Est_T_Q  <->  Projs
          Type = Type    
          Qrtr = Qrtr

      Calculations:
      Calc field  Diff_Q:      Est_Q::Hrs_Sum - Projs::Total_Sum        (unstored, from "Projs")
      Calc field Diff_T:        Est_T_Q::Hrs_Sum - Projs::Total_Sum    (unstored, from "Projs")

       

      Thanks,

      J

      Diff_combo.png

        • 1. Re: Calculation on summary fields not working
          philmodjunk

          Calc field  Diff_Q:      Est_Q::Hrs_Sum - Projs::Total_Sum        (unstored, from "Projs")
          Calc field Diff_T:        Est_T_Q::Hrs_Sum - Projs::Total_Sum    (unstored, from "Projs")

          Am I correct that both Hrs_Sum and Total_Sum are summary fields?

          When you use summary fields in calculation, the value returned is the total for the current found set, not the sub total from a sub summary part. To get a sub total, you'll need to use get Summary and yes, sorting, which changes the grouping will affect the values returned. You may need either separate layouts for your different sort orders in order to compute accurrate differences for each sort order.

          • 2. Re: Calculation on summary fields not working

            Yes, those are both summary fields.  You said "may need either...", but then seemed to leave out the "either" option.  :)  Is there another option?

             

            And "use get Summary...".  I couldn't find a get(summary) function in the reference docs; I am assuming that that is what you were implying, but that appears to an inaccurate assumption. 

             

            Thanks,

            - J

            • 3. Re: Calculation on summary fields not working
              philmodjunk

              Yeah, I started to mention an option but the more i thought about it the less I liked it:

              If you use a script to sort your records it could set a global variable to a value that records the sort order. Your difference calculation could then use If or Case to test the value of this variable and use different getSummary functions--specifying the break field appropriate to the sort order to compute the difference.

              Hmmm, Maybe that's not such a bad idea after all...

              • 4. Re: Calculation on summary fields not working

                I don't think that I am getting the whole picture here...

                What does recording the Sort order gain me?  And then the getSummary functions again...  :)

                If the current summary fields, when used in a calc, result in the total for the found set (and not just the sub-summary lines), wouldn't you have to go off and sort by each sub-sub-summary part in order to get the correct values for that grouping, and then use that in some manner?

                 

                Thanks,

                -J

                • 5. Re: Calculation on summary fields not working
                  philmodjunk

                  Subtotals--whether displayed in a Sub Summary layout part or calculated by a getSummary function rely on the current sort order to determine the "grouping" on which the subtotals arebased. Change the sort order and you get different groups--upon which the totals for each such group change. The "break" field in the sub summary part is the same as the "when sorted by" field specified for the sub summary part.

                  If you don't include the "sorted by" field in your sort order, the sub summary part disappears. If you don't include the break field in your sort order, the getsummary function returns null.

                  If the current summary fields, when used in a calc, result in the total for the found set (and not just the sub-summary lines), wouldn't you have to go off and sort by each sub-sub-summary part in order to get the correct values for that grouping, and then use that in some manner?

                  An understandable question, but no. As long as the field is included in the sort order, you'll get a value returned, but it may not be the same value as a different sort order that also includes the same field. GetSummary functions within your calculation to return the same subtotals as a getSummary part--provided the "break" and "When sorted by" fields are the same.

                  • 6. Re: Calculation on summary fields not working

                    I am groking it more now, I think!  It helps when I realized that what you were talking about was a funtion called "getSummary()"!  I was starting to think that this was a function you were implying I should write.  :)

                     

                    With a two layer sub-summary, just giving the break field of the second layer doesn't seem like it would work, because wouldn't that return the result for all records in the found set, and not just in that sub-set?  I.e. if my primary sort is Quarter, and then 2nd layer is Type, if I run getSummary(Type), wouldn't it return values for ALL quarters worth of records (assuming that I have more than one Quarter of results)?  (I obviously haven't tested this; just thinking through what I have read - here and in the docs - and trying to envision the end result.  I will test when I have more time.)

                     

                    Thanks,

                    J

                    • 7. Re: Calculation on summary fields not working
                      philmodjunk

                      If you have a subsummary layout part with "Quarter" as the "when sorted by" field. GetSummary(SummaryField ; Quarter ) will return exactly the same sub total that you see when you put SummaryField into that sub summary part.

                      After posting my last response and thinking about this, I don't think you need to get fancy with detecting sort orders and If/Case functions. Just define one calculation field that uses GetSummary to compute the sub totals before subtracting them for each sub summary part where you need to see this difference. The calculations will be the same except for the break field specified. Then put your calculation fields into the appropriate sub summary layout parts.

                      • 8. Re: Calculation on summary fields not working

                        I think I am getting half of it to work.  But the half that works does appear to be working as desired.  Cool.


                        Now, the part that doesn't:  these are the fields that summarize data from another T.O.  I am not getting invalid data, I just end up with blank fields.
                        My layout is based on the "Projects" table.  The data stored in this table is summarized correctly when sorted by the different break fields.  "Projects" is related to two other TOs (both are based on the same table) by two relationships (as noted in the original post):
    

                        Est_Q  <->  Projs

                        
        Qrtr = Qrtr


                        Est_T_Q  <->  Projs


                                Type = Type

                                Qrtr = Qrtr



                        These are the same break field/sorting that we are doing on the layout, too.  Here are my summary fields calcs:
                        GetSummary( Est_Q::Hours_Sum ;  Quarter )
                        GetSummary( Est_T_Q::Hours_Sum ; Type )

                        I have placed the field that breaks on Quarter in the Quarter sub-summary line, and the one for Type in the Type sub-summary.


                        This is essentially the exact same set up that I have for the Actual hours, except that those are stored in the Projects table (i.e. the same table as the layout is based on).  The summary field used in the GetSummary() call is different, of course.  Does GetSummary() not work across relations?  It has to be located in the "Projects" table because it has to be in the same table as the break field.

                         

                        Thanks,

                        J

                        • 9. Re: Calculation on summary fields not working

                          I keep seeing references to using a self-join to accomplish functionality similar/identical to getSummary().  Can you explain how that might work here?  Since I am looking at a different table, it doesn't seem like it would work.

                           

                          • 10. Re: Calculation on summary fields not working
                            philmodjunk

                            GetSummary only works for summary fields defined in the same table. They won't work when referring to a related table--even when it's the same datasource table. That's because the function cannot access any kind of "sort order" in the set of related data like it can with the layout's found set.

                            There are two ways to get the sum of all records that are related to the current record. Both would work in this case since you match to all records of the same quarter and all records of the same Type and Quarter.

                            Sum ( Est_Q::Hours ) will compute the total hours for all records of the same quarter.

                            But you already have a summary field you can use. Thus you can also put

                            Est_Q::Hours_Sum

                            as the sole term in your calculation field and it will return the same total.

                            • 11. Re: Calculation on summary fields not working

                              Well, I implemented your suggestion and things seemed to be working out well.  Thanks for the hints.

                               

                              However, I then noticed an issue with my other sub-summary parts.  It was in the original posting (which is good, because it means I didn't mess up something that was previously working), but I guess I had concentrated on the other task that I had missed it. 

                              If you look back up at the image on the original, you can see that in the Quarter -> Proj sorts (i.e. quarter sorted first, then project type), the values look fine:  the secondary sub-summary lines are LESS than the primary line.  But it is the other way around if it is sorted Proj -> Quarter:  the secondary SS values are HIGHER than the primary ones.  I attached a new screen capture of two views (I removed the Body to make the SSs clearer).

                               

                              The two 'capacity' totals on the right are the two new fields that I implemented by your suggestions above, in order to make the calculation work.  The calculation DOES appear to be working in all cases, but now one of the values used in the calc is not updating correctly.  More issues with trying to summarize values from a different table, it seems.

                              I will do some more poking at it to see if I can make it work.

                               

                              - J