FM 8.5 adv. I have a calculation GetSummary field in a subsummary part that's sorted by State. I need its grand summary in a grand subummary part. I've tried summary and getsummary calculations but nothing works. They don't total properly. Is there any way I can do this? In general I'm having a difficult time using GetSummary in complex calculations.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Difficulty with GetSummary fields
- Thread starter john9210
- Start date
The first question that comes to mind is if you can do math with summary fields.
The answer is both yes and no.
Summary fields can be, but should NOT be, used directly in calculation formulas.
There's nothing to prevent you from doing so, but it's usually nonsensical to do so.
Inside a calculation formula, a summary field is evaluated as the aggregate result of the entire found set.
Thus, if you were to define a field called AverageIncomeIncrease as AverageIncome01 - AverageIncome02, the result could be -0.25 no matter what record you were viewing or in what layout part you placed the field.
The formula will not properly generate subsummary values.
The solution is to use the GetSummary() function.
It takes 2 parameters; a summary field and a breakfield.
When the current found set is sorted by the breakfield, the function returns the same value that would appear if the summary field were used in a subsummary layout part (based on the same breakfield of course)
If the found set is not sorted by the breakfield, the function reurns the value of the summary field over the entire found set.
And this is the SAME value returned by simply putting a summary field in a calculation without the GetSummary() function.
To produce a summary IncomeIncrease at State level you could use a calcualtion along these lines:
GetSummary(AverageIncome01 ; State) - GetSummary(AverageIncome02 ; State).
This field could be placed in the trailing subsummary part to display the results for each State.
The fact that you have to name the breakfield explicitly means that calculations involving summary fields aren't as reusable as summary fields themselves.
If you were making another report showing income by gender, you would need a new calculation field AverageIncomeGender that specified gender as breakfield.
You can dynamically assemble an GetSummary function and use the Evaluate() function to return proper values.
Although the purpose of using a GetSummary() function is to produce a value appropriate for display in a subsummary part, the values also display properly when placed in a body part.
Each of the records of the subgroup knows the aggregate value for its particular set. This is distinctly different from the result of simply placing a summary field into a body part. Then the value displayed will represent an aggregation of the entire found set.
HTH
The answer is both yes and no.
Summary fields can be, but should NOT be, used directly in calculation formulas.
There's nothing to prevent you from doing so, but it's usually nonsensical to do so.
Inside a calculation formula, a summary field is evaluated as the aggregate result of the entire found set.
Thus, if you were to define a field called AverageIncomeIncrease as AverageIncome01 - AverageIncome02, the result could be -0.25 no matter what record you were viewing or in what layout part you placed the field.
The formula will not properly generate subsummary values.
The solution is to use the GetSummary() function.
It takes 2 parameters; a summary field and a breakfield.
When the current found set is sorted by the breakfield, the function returns the same value that would appear if the summary field were used in a subsummary layout part (based on the same breakfield of course)
If the found set is not sorted by the breakfield, the function reurns the value of the summary field over the entire found set.
And this is the SAME value returned by simply putting a summary field in a calculation without the GetSummary() function.
To produce a summary IncomeIncrease at State level you could use a calcualtion along these lines:
GetSummary(AverageIncome01 ; State) - GetSummary(AverageIncome02 ; State).
This field could be placed in the trailing subsummary part to display the results for each State.
The fact that you have to name the breakfield explicitly means that calculations involving summary fields aren't as reusable as summary fields themselves.
If you were making another report showing income by gender, you would need a new calculation field AverageIncomeGender that specified gender as breakfield.
You can dynamically assemble an GetSummary function and use the Evaluate() function to return proper values.
Although the purpose of using a GetSummary() function is to produce a value appropriate for display in a subsummary part, the values also display properly when placed in a body part.
Each of the records of the subgroup knows the aggregate value for its particular set. This is distinctly different from the result of simply placing a summary field into a body part. Then the value displayed will represent an aggregation of the entire found set.
HTH
- Thread starter
- #3
Not sure, I don't know the structure nor your fielddefinitions. I would say try it out and see what the result is.
This will also help you to understand what is NOT working and why.
Sometimes that is more important to know as developer
.
If you want a total of (Item) of all States in a Country, make a calc field Sum(Item of State).
Put this field in a Sub-summary part by Country(Leading) on your layout.
This will also help you to understand what is NOT working and why.
Sometimes that is more important to know as developer
.If you want a total of (Item) of all States in a Country, make a calc field Sum(Item of State).
Put this field in a Sub-summary part by Country(Leading) on your layout.
- Thread starter
- #5
Jeane,
Maybe this example will illustrate what I'm trying to do.
I have two columns. Col1 is the name of a state (input) and Col2 is a calculation field involving getsummaries.
Col1 Col2
AL 0
AL 0
FL 8.53
Fl 8.53
GA 6.98
GA 6.98
GA 6.98
IN 14.91
IN 14.91
Note that each state has the same value in col2.Here's the part I can't figure out how to do. What I need is a field for a grand subsummary of Col2 that totals only one value from each state. It would take sum the following:
Col1 Col2
AL 0
FL 8.53
GA 6.98
IN 14.91
yielding a total of 0+8.53+6.98+14.91 (45.93)
Thanks for your help.
Maybe this example will illustrate what I'm trying to do.
I have two columns. Col1 is the name of a state (input) and Col2 is a calculation field involving getsummaries.
Col1 Col2
AL 0
AL 0
FL 8.53
Fl 8.53
GA 6.98
GA 6.98
GA 6.98
IN 14.91
IN 14.91
Note that each state has the same value in col2.Here's the part I can't figure out how to do. What I need is a field for a grand subsummary of Col2 that totals only one value from each state. It would take sum the following:
Col1 Col2
AL 0
FL 8.53
GA 6.98
IN 14.91
yielding a total of 0+8.53+6.98+14.91 (45.93)
Thanks for your help.
0+8.53+6.98+14.91 is at this side of the globe (30.42) and not (45.93) or are there hidden fields ?
Apart from a strange structure, a quick bandage for your problem is to isolate all the unique records by script and make a pre-view on the found set.
Make first two more fields:
mark - type text
flag - type global text
For the preview we need two more fields:
sumNumber_cn - calc number = Sum(yourNumberFieldFrom Col2)
totalSumNumber - Summary = Total of sumNumber
Make a layout (Show Result) with a Header part and put the labels State and Number in it.
Make a Sub-summary by State (leading)part and drop the fields State and Number in it.
Make a Trailing Grand Summary part and drop the totalSumNumber field in it, together with a label, Total or something.
Make a script YourScriptName
Show All Records
Sort Records (Specified Sort Order: State: ascending (Restore, No dialog)
Go to Record/Request/Page (First)
Replace Field Content ( flag: Replace with calculation:"")
(No Dialog)
Set Field (flag;State)
Loop
Go to Record/request/Page (Next;Exit after last)
If(flag = State)
Set Field (mark;1)
Else
Set Field(flag,State)
End If
End Loop
Perform Find(Specified Find Request:Find Records; Criteria:mark:"1") (Restore)
Show Omitted Only
Go to layout (Show Result)
Sort Records (Specified Sort Order: State;ascending) (Restore; No dialog)
Enter Preview Mode
This will give you all the States just 1 time with the total of the number field for the found set of unique records.
HTH
Apart from a strange structure, a quick bandage for your problem is to isolate all the unique records by script and make a pre-view on the found set.
Make first two more fields:
mark - type text
flag - type global text
For the preview we need two more fields:
sumNumber_cn - calc number = Sum(yourNumberFieldFrom Col2)
totalSumNumber - Summary = Total of sumNumber
Make a layout (Show Result) with a Header part and put the labels State and Number in it.
Make a Sub-summary by State (leading)part and drop the fields State and Number in it.
Make a Trailing Grand Summary part and drop the totalSumNumber field in it, together with a label, Total or something.
Make a script YourScriptName
Show All Records
Sort Records (Specified Sort Order: State: ascending (Restore, No dialog)
Go to Record/Request/Page (First)
Replace Field Content ( flag: Replace with calculation:"")
(No Dialog)
Set Field (flag;State)
Loop
Go to Record/request/Page (Next;Exit after last)
If(flag = State)
Set Field (mark;1)
Else
Set Field(flag,State)
End If
End Loop
Perform Find(Specified Find Request:Find Records; Criteria:mark:"1") (Restore)
Show Omitted Only
Go to layout (Show Result)
Sort Records (Specified Sort Order: State;ascending) (Restore; No dialog)
Enter Preview Mode
This will give you all the States just 1 time with the total of the number field for the found set of unique records.
HTH
- Thread starter
- #7
- Thread starter
- #8
My response
Hi Jean
Your approach didn’t quite work out. The field in the trailing grand subsummary produced incorrect results. I suspect because of the way the number in col2. I’ll have to go a little deeper into what I’m doing.
I’m making a calculation for State Tax Returns for a trucking business. The taxes are calculated from the mileage and gas purchased in each state. Each state has it’s own tax rate. The col2 calculation is for a penalty due with late tax returns. The actual formula in col2 is:
Penalty = Case(Taxdue>0,TaxDue*Penalty rate,0)
Where
Taxdue = [GetSummary(SumMiles,State)/MPG – GetSummary(SumGals,State)]*State tax rate
Note that:
Penalty rate = constant (usually 1%)
MPG = number = global = a constant set by script. Its determined from the total trip miles and fuel used.
State tax rate varies from state to state
So, when I use the script you recommend, I get the following results for Penalty in the subsummary part:
AL 0
FL .09
GA .07
IN .15
OH .12
OH* .02
These are correct. However, the value in the trailing grand subsummary field (SumPenalty_cn) is wrong. It yields 0.02, but should be 0.45 (in my neck of the woods).
Any suggestions?
Hi Jean
Your approach didn’t quite work out. The field in the trailing grand subsummary produced incorrect results. I suspect because of the way the number in col2. I’ll have to go a little deeper into what I’m doing.
I’m making a calculation for State Tax Returns for a trucking business. The taxes are calculated from the mileage and gas purchased in each state. Each state has it’s own tax rate. The col2 calculation is for a penalty due with late tax returns. The actual formula in col2 is:
Penalty = Case(Taxdue>0,TaxDue*Penalty rate,0)
Where
Taxdue = [GetSummary(SumMiles,State)/MPG – GetSummary(SumGals,State)]*State tax rate
Note that:
Penalty rate = constant (usually 1%)
MPG = number = global = a constant set by script. Its determined from the total trip miles and fuel used.
State tax rate varies from state to state
So, when I use the script you recommend, I get the following results for Penalty in the subsummary part:
AL 0
FL .09
GA .07
IN .15
OH .12
OH* .02
These are correct. However, the value in the trailing grand subsummary field (SumPenalty_cn) is wrong. It yields 0.02, but should be 0.45 (in my neck of the woods).
Any suggestions?
>>It yields 0.02, but should be 0.45
And that value is correct. It returns the value from field OH*, which is probably the last in the found set.
It's not SumPenalty_cn that you have to put in the Trailing Grand Summary part, but TotalSumNumber.
TotalSumNumber is a Summary field = total of sumNumber or for you, if I follow your fieldefinitions, = total of SumPenalty_cn.
See how that goes.
And that value is correct. It returns the value from field OH*, which is probably the last in the found set.
It's not SumPenalty_cn that you have to put in the Trailing Grand Summary part, but TotalSumNumber.
TotalSumNumber is a Summary field = total of sumNumber or for you, if I follow your fieldefinitions, = total of SumPenalty_cn.
See how that goes.
- Thread starter
- #10
I made a file based upon your fields and I get the right values. There's something wrong in your file.
Let's start debugging
state - text
Penalty - calculation, result number = your calculation
This is the value in your Col2, right ?
You need a calculation field, sumPenalty = Sum(Penalty)
You need a Summary field (call it totalPanalties), type Summary = Total of Sum(Penalty)
A layout with 3 parts, Header, Sub-summary by State (leading) and a Trailing Grand Summary.
Header: only labels for the fields (can be State and Penalty)
Sub-summary: field State and Penalty
Trailing Grand Summary: a label (f.i. Total) and field
totalPenalties.
That's exactly the set-up I have here, and it's working.
If not, we could find a way so I can send you a sample file....
Let's start debugging
state - text
Penalty - calculation, result number = your calculation
This is the value in your Col2, right ?
You need a calculation field, sumPenalty = Sum(Penalty)
You need a Summary field (call it totalPanalties), type Summary = Total of Sum(Penalty)
A layout with 3 parts, Header, Sub-summary by State (leading) and a Trailing Grand Summary.
Header: only labels for the fields (can be State and Penalty)
Sub-summary: field State and Penalty
Trailing Grand Summary: a label (f.i. Total) and field
totalPenalties.
That's exactly the set-up I have here, and it's working.
If not, we could find a way so I can send you a sample file....
- Thread starter
- #12
Penalty is the field in col2. It's a calculation field.
I'm still getting 0.9
However, I've noticed the last state OH is missing. The loop may be exiting before the last record. Also, I've had to disable the Show Omitted script step.
If you could send a sample, that would be great! I haven't figured out how to send attachments with postings on this site. Is there a way?
John
I'm still getting 0.9
However, I've noticed the last state OH is missing. The loop may be exiting before the last record. Also, I've had to disable the Show Omitted script step.
If you could send a sample, that would be great! I haven't figured out how to send attachments with postings on this site. Is there a way?
John
Something strange is going on.
If it's working here it should work for you also.
If you want, send a clone or stripped part of your file, no records to montana_c50 at yahoo period com.
I want to take a look at the structure and I can send you a sample file.
HTH
If it's working here it should work for you also.
If you want, send a clone or stripped part of your file, no records to montana_c50 at yahoo period com.
I want to take a look at the structure and I can send you a sample file.
HTH
- Thread starter
- #14
- Status
Similar threads
- Locked
- Question
- Locked
- Question