Maximum Value Formula

[jbhsuman]

Version CR9

I am trying to generate a report that details the payment of individual items. Depending on the payer, the payment amounts will vary. I would like the report to show the average payment amount along with the highest payment amount. The report is grouped by item code and invoice and the average amounts and highest amount paid is subtotaled by item code.

I first tried to use the running total function choosing the payment field as the summary field and maximum as the summary type but it seems to report the first record in the group even though there are other payments within the grouping that are higher.

I then tried creating a formula that compares the value of the current payment to the previous payment and depending on the results will return the higher value. The formula works fine until it changes to the next group. Right now, when the group changes, the formula compares the current payment to the previous payment from the last group. Is there a function to test for when the group value changes and evaluate accordingly. Below is an example of the code.

 Global currencyVar CurrPmt :=0;
Global CurrencyVar MaxPmt := 0;

CurrPmt := {Chg_Pmt_Trans.payment_EXTENDED};

if Not OnFirstRecord then
    if {Chg_Pmt_Trans.payment_EXTENDED} < previous ({Chg_Pmt_Trans.payment_EXTENDED})Then
        MaxPmt := CurrPmt Else //{Chg_Pmt_Trans.payment_EXTENDED} Else
        MaxPmt := previous ({Chg_Pmt_Trans.payment_EXTENDED})
    Else
    MaxPmt := {Chg_Pmt_Trans.payment_EXTENDED};

Any help with either solution would be greatly appreciated.

Thanks

Joe

 

[Naith]

Impressive looking formula, but it doesn't need to be half that complex.

Generate your maximum payment by group like this:

Maximum({Payment},{YourGroupField})

Generate your average like this:

Sum({Payment},{YourGroupField})/Count({ItemID},{YourGroupField})

Naith

 

[jbhsuman]

Naith,

Thank you for your help. I tried your Maximum funtion and it returns the first payment not the maximum payment.

For example,
Payment 1 = 70.00
payment 2 = 90.00
payment 3 = 85.00

In this group, the maximum value displayed is 70.00. Why would this be?

Thanks

Joe

 

[jbhsuman]

BTW, this is the same results I am getting when I tried using the running total function.

TY

 

[Naith]

Give me a breakdown of your report.

e.g.

GH1 (ItemCode)
GH2 (InvoiceNo)
Details (Payments) - (maximum and avg formulas placed here)
GF2
GH1

Additionally, copy and paste what your current formulas for deriving the max and avg say.

Naith

 

[jbhsuman]

Thanks again for helping. You pretty much defined the report structure.

GH1{Item_Code}
GH2 {Invoice}
D {payment.Item_code, charge amt, payment}
GF2
GF1

Maximum ({Chg_Pmt_Trans.payment_EXTENDED},{Chg_Pmt_Trans.charges_CODE})

I have the code in the GF1 section of the report. As for the average, I am using a running total field and that appears to be working fine.

Anything jump out at you as wrong?

TY

 

[Naith]

{Chg_Pmt_Trans.charges_CODE} is the name of the Item Code and not the Invoice Charge No, right?

If so, can you confirm what happens if you force the evaluation time of the formula to start with WhilePrintingRecords;

 

[lbass]

That you are grouping by invoice number within the item code group suggests that there may be more than one payment per invoice. If that is the case, then the maximum would only be picking up the maximum of one record within the invoice group, not the total for the invoice itself. For example:

Item 001 Total Payments
Inv. 123 $155
001 $70
001 $35
001 $50
Inv. 456 $ 90
001 $90

Maximum ({table.payment},{table.itemID}) = $90

Could this be the issue?

-LB

 

[jbhsuman]

Naith, you are correct, {Chg_Pmt_Trans.charges_CODE} is the item code. I will try your suggestion regardin evaluation time.

LB, you are correct that there may be multiple payments (different types, ie, write offs, discounts, etc.) Each payment is defined by a seperate field, Code Type. however, on this report I use the selection filter to filter out all with the exception of one type of payment.

TY all for assisting me. I will post my results using the evaluation time.

Joe

 

[jbhsuman]

Naith,

I tried your suggestion and inserted the WhilePrintingRecords evaluation function.

WhilePrintingRecords;
Maximum ({Chg_Pmt_Trans.payment_EXTENDED},{Chg_Pmt_Trans.charges_CODE})

But there is no change in the results. I also tried moving the formula to the detail section with no change in results. Any other suggestions?

Thanks again for the assistance.

Joe

 

[Naith]

If you don't mind me taking a look at this for you, save your report with data and send it to me at naith@angelic.com.

Naith

 

[jbhsuman]

Naith, Thank you again for your help. I will send the report to you shortly.

Joe

 

[Naith]

I sent you a sample report. I think your problem is that you're using variables which are resetting at every stage of the count.

You don't need to use variables to retrieve average or maximum results, as there are functions for both of these, which I've included in the report.

Additionally, I'm not sure your maximum formula is quite on the money, as it returns questionable results. If you were to use a formula rather than the function, I imagine your formula would have to look more like this:

WhilePrintingRecords;

CurrencyVar MaxPmt2;

If OnFirstRecord
Then MaxPmt2 := {Orders.Order Amount}
Else
    If {Orders.Order Amount} > MaxPmt2
    Then MaxPmt2 := {Orders.Order Amount}
    Else MaxPmt2 := MaxPmt2;

Let me know if you continue to have problems.

Naith

 

[jbhsuman]

Naith,

I think I may have found the source of my problem. I am dealing with curreny values. When making a payment, payments appear as negitive values. This means that the greater value is smaller payment. That's is why the calculations I have been trying appear to return the opposite of what I expect.

Thanks for your help.