Maximum value of a sum 1

[DJWheezyWeez]

I have a report that is grouping a quantity by date and by machine, for example 100000 quantity produced on 8/28/14 on machine abc.

What I'm looking for is a way to only show the largest summed quantity for each machine using something like maximum(sum(qty, date), machine) but I get the error "This field cannot be summarized". Is there a way around this error?

Any help would be appreciated, thanks.

 

[BettyJ]

Can you please explain what you are looking for, with sample data?

 

[BobSuruncle]

Hi,
You can't summarize a summarized field - at least not directly.
One method would be to use 3 formulas as follows:

@1_ResetValuesForDateGroup
Place this formula in the group header for the Date group

WhilePrintingRecords ;
If NOT InRepeatedGroupHeader then
(
Numbervar Array amt ;
NumberVar c := DistinctCount({@Machine},{Orders.Order Date},"daily") ;
Redim amt[c];
NumberVar i := 1 ;
''
)
else ''

@2_CalcValues
Place this second formula in the group footer for the Machine group

WhilePrintingRecords ;
NumberVar array amt ;
NumberVar i;
amt[i] :=  ( Sum ({@UnitPrice}, {Customer.Customer Name}) ) ;
i := i + 1 ;

@3_DisplayMax
Place this third formula in the group footer for the Date group

WhilePrintingRecords ;
NumberVar array amt ;
maximum(amt)

Bob Suruncle

 

[DJWheezyWeez]

Thanks for responding so quickly.

Bob,
I copied your formulas, substituting in my own fields, and put them in the groups you mentioned but I'm not sure what each formula is doing. Formula @1_ResetValuesForDateGroup is blank, @2_CalcValues shows a 2, and @3_DisplayMax shows all 0. Unless I'm putting these in the wrong grouping or substituted in the wrong fields, I'm not sure why it's wrong.

Betty,
Here is some sample data that I'm working with:

[indent]              [u]Machine[/u]      [u]Qty[/u]       [u]Date[/u][/indent]
GH1:Machine      ABC
GF2:Date         ABC          1000      8/28/14
GF2:Date         ABC          900       8/29/14
GF2:Date         ABC          1100      8/30/14
GF2:Date         ABC          750       8/31/14

GH1:Machine      DEF
GF2:Date         DEF          300       8/28/14
GF2:Date         DEF          550       8/29/14
GF2:Date         DEF          400       8/30/14
GF2:Date         DEF          500       8/31/14

The detail section is suppressed but shows all the specific line detail which is all summed up by date and shown in GF2. What I'm trying to do is only show the line with the largest qty for each Machine group, GH1. The end result would show only the one line that has the largest qty by machine and the date it happened. Something like:

[indent]              [u]Machine[/u]      [u]Qty[/u]       [u]Date[/u][/indent]
GF2:Date         ABC          1100      8/30/14
GF2:Date         DEF          550       8/29/14

Does this help illustrate what I'm trying to do?

 

[BobSuruncle]

Hi,
You mentioned in your initial post that you were grouping on Date and Machine, so I assumed (bad, bad) that Date was Group 1 and Machine was group 2.

If I can summarize what you are looking for, you want to display the largest number summed for the Date group within the Machine group.

Here is another stab at it:

@1_ResetValuesForMachineGroup
This will reset all variables used to their initial values every time that the Machine group changes.
This formula will go into the group header for the Machine group.
We're checking to see if the Group Header is repeating. If so, we don't want to reset the variables.
We're creating an array called "amt" to store the summary values for each date group.
We're setting the dimension of the array to the total number of different dates that will be in a particular Machine group.
We're setting a counter called "i", that we'll increment in the next formula, so that we store the value for each Date group in its own element in the array.

WhilePrintingRecords ;
If NOT InRepeatedGroupHeader then
(
Numbervar Array amt ;
NumberVar c := DistinctCount({Orders.Order Date},{@Machine}) ;
Redim amt[c];
NumberVar i := 1 ;
''
)
else ''

@2_CalcValues
This formula will be placed in the Group Footer for the Date group.
It will place the summarized value for the Date group into the array.
The counter "i" will be incremented so that the value for the next Date group will be placed into its own element in the array.

WhilePrintingRecords ;
NumberVar array amt ;
NumberVar i;
amt[i] :=  ( Sum ({@UnitPrice}, {Orders.Order Date},'daily') ) ;
i := i + 1 ;

@3_DisplayMax
Finally, we use the Maximum() function to pull the largest number out of our array to display it.
This formula would be placed in the group footer for the Machine group.

WhilePrintingRecords ;
NumberVar array amt ;
maximum(amt)

Bob Suruncle

 

[DJWheezyWeez]

Thanks, that's perfect and very well explained. Any chance we could use similar logic or another other way to display the date that the largest qty sum occurred on?

 

[BobSuruncle]

slightly modified formulas, plus one extra to display the date

@1_ResetValuesForMachineGroup

WhilePrintingRecords ;
// Check to see that the Group Header is NOT repeated
// Will only occur if "Repeat Group Header on Each Page" is turned on for the group
If NOT InRepeatedGroupHeader then
(
Numbervar Array amt ; // Create an array in which to store the sums for each date
DateVar array mydate ; // Create an array to store the dates
NumberVar c := DistinctCount({Orders.Order Date},{@Machine}) ; // store the number of date groups for each machine
Redim amt[c]; // Redimension the array to the same number from above
Redim mydate[c] ; // Redimension the array to the same number from above
NumberVar i := 1 ; // Create a counter that will be used to populate the appropriate element number of each array
'' // Just returns a blank string
)
else ''

@2_CalcValues

WhilePrintingRecords ;
NumberVar array amt ; // reference the array created in forumla number 1
DateVar array mydate ; // reference the array created in forumla number 1
NumberVar i;
amt[i] :=  ( Sum ({@UnitPrice}, {Orders.Order Date},'daily') ) ; // populates the array with the amount for the current Date group
mydate[i] := Date({Orders.Order Date}) ; // populates the array with the date for the current Date group
i := i + 1 ; // increments the counter so it's ready for the next Date group

@3_DisplayMax

WhilePrintingRecords ;
NumberVar array amt ;  // reference the array created in formula number 1
maximum(amt) // pull out the largest value found in the array

@4_Display DateOfMax

WhilePrintingRecords ;
NumberVar array amt ; // the array that contains the values
Numbervar a := maximum(amt) ; // Store the highest value to a variable called "a"
Datevar maxdate ; // create a variable to hold the date where the maximum value occurs
NumberVar c ; // This is the variable we created in the first formula that is the number of dates for each Machine
Local NumberVar b ; // used as a counter to loop through the array of amounts (amt)
Datevar array mydate ; // This is the date array that we created in the first formula

// if the array element number "b" equals the maximum amount, then 
// record the date that is in the same element number in the date array
for b := 1 to c do
(
if amt[b] = a then maxdate := mydate[b] else maxdate := maxdate 
);

maxdate // show the value for the maxdate variable

Bob Suruncle

 

[DJWheezyWeez]

Perfect. That's exactly what I was looking for. I appreciate all the help