Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zagzebski
Contributor

Using Peek to get a Cummuliative Number

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.

Tags (1)
3 Replies
Not applicable

Re: Using Peek to get a Cummuliative Number

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
Contributor

Re: Using Peek to get a Cummuliative Number

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.

Not applicable

Re: Using Peek to get a Cummuliative Number

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

Community Browser