Formula in Group Footer adding prior value

[marylally]

I have a report that is grouped by Emp and Weeks
I have created a formula that calculated an average.

@vpg
sum ({@SalesVol}, {PAYABLES_LEDGER.PAY_WK})/Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK})

This works fine and calculates correctly for all emp

The problem is in my @sumavg formula that is located in the Group Footer

@sumavg
whileprintingrecords;
numbervar sumavg := sumavg + {@VPG}/2;

This formula works correctly for the first employee but addes the previous value to the next employee.

Any help would be greatly appreciated.





MaryBeth

 

[briangriffin]

You need to reset your variable at each change of group - try putting this formula in the group header:

whileprintingrecords;
numbervar sumavg := 0;

 

[marylally]

THanks! That worked! Much appreciated Mary

MaryBeth

 

[marylally]

Now I have an error where there is only one record for vpg
my @sumavg is zero.

Week 1 vpg = 0
Week 2 vpg = 2283

Sumavg should = 2283/2 = 1142



MaryBeth

 

[briangriffin]

Trap for zero values in your formula:

if Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK}) = 0 then 0

else

sum ({@SalesVol}, {PAYABLES_LEDGER.PAY_WK})/Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK})

 

[marylally]

It works but not on every one.?

MaryBeth

 

[briangriffin]

Please show the contents of your {@in period counter} formula if this doesn't work:

if Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK}) = 0 or isnull(Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK}))

then 0

else

sum ({@SalesVol}, {PAYABLES_LEDGER.PAY_WK})/Count ({@in period counter}, {PAYABLES_LEDGER.PAY_WK})

 

[marylally]

Didn't work,

@in period
//IIF ({OPP0100_HIST_LAST.BKDATE}>={PAY_PERIOD.BEGIN_DT},True , False)
//IIF ({PAYABLES_LEDGER.PAY_DATE}>={PAY_PERIOD.BEGIN_DT},True , False)
IIF ({PAYABLES_LEDGER.PAY_DATE}={PAY_PERIOD.PAY_DATE},True , False)

@in period counter
IIF ({@in period},1 ,0 )

@prior period counter

//IIF ({@in period},0 ,1 )
If {PAYABLES_LEDGER.PAY_DATE} ={@calPriorPayDate}
then NumberVar xPriorCnt := xPriorCnt + 1;

Mary




MaryBeth

 

[briangriffin]

Can you please provide sample data for both detail and group sections, including what you want your results to be?

 

[marylally]

Group Header 1
Emp_id 2007 Pamela L.
Group Header 2

Week 28 Tours(period counter) Sales VPG
13 10,400 800
Week 29 19 33,740 1776

Detail section (hidden)contains in period ctr and prior ctr

Group Footer 2
Total Tours 32
Avg VPG 1288 (desired result) not calculating on some records where there is no value for sales on one week but a value on the other week.



MaryBeth

 

[briangriffin]

It looks like you're trying to display a running average of VPG by week, and a running total of tours by week, displayed in GR2 - is that right?

Tours:

whileprintingrecords;
numbervar v_tours;
v_tours := v_tours + sum({toursfield,weekgroup};
v_tours

VPG Avg:

whileprintingrecords;
numbervar x;
numbervar y;
x:= x + 1;
y:= y + {@VPG};
y/x

In GH1 reset the variable:

whileprintingrecords;
numbervar x := 0;
numbervar y := 0;
numbervar v_tours := 0;


I'm sure there's more to it than that, so if you can upload the report with saved data to shared storage I'd be happy to take a closer look.

If not, please provide some additional sample data for an employee where the calculations are not working properly.