Using Peek to get a Cummuliative Number

[zagzebski]

I have the following script

CummulativeCommission:

Load
[Temp Dept],
[Temp Cust],
[Temp Commission DeptCust],
[Temp Commission Dept],
if(previous([Temp Dept])=[Temp Dept],rangesum([Temp Commission DeptCust],peek('[Temp CumCommission]')),[Temp Commission DeptCust]) as [Temp CumCommission],
if(previous([Temp Dept])=[Temp Dept],rangesum([Temp Commission DeptCust],peek('[Temp CumCommission]')),[Temp Commission DeptCust])/[Temp Commission Dept] as [Commission Dept CummPerc]
Resident ProductionByDeptByCust
Order by [Temp Dept] asc, [Temp Commission DeptCust] desc;

I want the field "[Commission Dept CummPerc]" to return a cumulative percent by grabbing the previous record (when "Temp Dept" = the previous "Temp Dept" field.

Unfortunately it is not working properly and the "Temp CumCommission" field is not bringing in not cummulating the commssion in the "Temp CumCommission" field it is only bringing in the current records Commission. Botto line for some reason it is not performing the rangesum as part of the if statement but I can't figure out why.

[#2]

Hi Zagzebski,

I've attached an example of accumulating fields in the script.  In your script, try the following:

if(recno()<>1 and peek('[Temp Dept]')=[Temp Dept],rangesum([Temp Commission DeptCust],peek('[Temp CumCommission]')),[Temp Commission DeptCust]) as [Temp CumCommission],

Regards, Karl

[zagzebski]

Thanks for trying to help Karl. I changed my script to match what you send and it didn't change anything. What is frustraitng is I had it working for a while and then all of a sudden it wasn't. It retur ns values that are based on the current rrow - nothing is accumulating.

[#4]

When you refer to the field in the peek() function don't put the square backets ([]).  Only use the single quotes (´´)

For example,

peek('Temp CumCommission')

Regards, Karl