Formula that references Max and Min summary values

[rmdalrk]

I have a formula called Average Days that displays the average number of days between invoices. Here is the formula:

{@DateDiff Spread}/Count ({ARFID.INVOICE_NUMBER}, {ARFID.CUST_PART_ID})

Here is the DateDiff Spread formula referenced above:

(Maximum ({ARFIM.INVOICE_DATE}, {ARFID.CUST_PART_ID})-Minimum ({ARFIM.INVOICE_DATE}, {ARFID.CUST_PART_ID}))+{@Current Date - Max}

The Average Days formula is working and displaying the correct values. However, I need to be able to use its results as part of an aglorithm formula that assigns weights to other values then displays 1 final value.

When I reference the Average Days formula in another formula, I get an error:

"A summary has been specified on a non-recurring field.
Details: @Sales Sum"

Sales Sum is the formula that should display the final value. I think the issue is that I'm trying to reference a formula that references Maximum and Minimum summary values, but I'm not sure how to work around this.


I haven't worked with variables much, but here is what I tried with no success.

I edited the Average Days formula to:

whileprintingrecords;

Shared NumberVar avgdays := {@DateDiff Spread}/Count ({ARFID.INVOICE_NUMBER}, {ARFID.CUST_PART_ID});

I then created a new formula called AvgDaysVariable and called the variable:

Shared NumberVar avgdays;

The variable value displays correctly but I'm still unable to use the variable value in another formula.

I tried using the variable in this formula:

({@QTY Previous 30 Days}*10)+{@QTY Previous Year}*1.5 + shared numbervar avgdays

I also tried calling the other formula field:

({@QTY Previous 30 Days}*10)+{@QTY Previous Year}*1.5 + {@AvgDaysVariable}

Either way, I'm still getting the same error.

Any help would be great.

 

[xxyyzz49]

In average days formula set a global variable to the value you want to use in another formula. Example formulas:

@AVERAGE DAYS
GLOBAL NUMBERVAR AverageValue
//processing
AverageValue := ResultsOfProcessing

@SALES SUM
GLOBAL NUMBERVAR AverageValue
//processing for example
SomeValue := AverageValue

 

[rmdalrk]

I tried that and I'm getting an error that the text after @AVERAGE DAYS does not appear to be part of the formula. Is there some syntax missing in your post?

 

[xxyyzz49]

My example was incomplete, apologies for rushing. The formulas below work in my report in that AVERAGE DAYS will set a global variable and SALES SUM can used the results and print "2". The examples are a demonstration of concept, passing values between formulas using a global variable. There are three scope types of variables LOCAL, GLOBAL and SHARED.
[ul]
[li]Global - The variable is available to formulas throughout the entire current report.[/li]
[li]Shared - The variable can be shared with a sub-report as well as the entire current report.[/li]
[li]Local - The variable is specific and can only be used in the formula in which it is defined.[/li]
[/ul]

If the scope keyword is omitted then the variable is GLOBAL, but use the keyword and make it obvious to others what you intended. For your example the results of one formula can be made available to another through one or more global variables.

Hope this is more understandable and you are able to use for your problem.

// formula @AVERAGE DAYS
// this is skeleton code, must adapt to own needs
//
GLOBAL NUMBERVAR AverageValue;
NUMBERVAR ResultsOfProcessing := 2;
//processing code goes here
AverageValue := ResultsOfProcessing;

// @SALES SUM
// this is skeleton code, must adapt to own needs
//
GLOBAL NUMBERVAR AverageValue;
//processing code goes in here 
AverageValue;

 

[rmdalrk]

Thank you for that. Just to clarity, I don't have a problem getting a variable to display, it's using the result of the variable in another formula that creates the problem and I think it has to do with the fact that the Average Days formula is using Maximum and Minimum functions which are summary functions.

 

[xxyyzz49]

Thank you for that. Just to clarity, I don't have a problem getting a variable to display, it's using the result of the variable in another formula that creates the problem and I think it has to do with the fact that the Average Days formula is using Maximum and Minimum functions which are summary functions.

Understood, having the formula simply display the value of the variable was one way to see that the value got passed from one formula to another. By using the value of a variable or several variables is a method to pass the needed results with out invoking the formula and producing the error message. Keep in mind there are evaluation function available to aid controlling when a formula is ran and placement in the various sections of the report is another.

So in my example AVERAGE DAYS runs saving the value of its processing in a global variable. Then SALES SUM runs using the value saved in the variable, printing it.