AnsweredAssumed Answered

Calculation on summary fields not working

Question asked by disabled_JustinClose on Apr 5, 2012
Latest reply on Apr 13, 2012 by disabled_JustinClose

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

Outcomes