topic Re: Using Peek to get a Cummuliative Number in QlikView

Using Peek to get a Cummuliative Number

zagzebski — Sat, 31 Aug 2013 22:52:41 GMT

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.

Re: Using Peek to get a Cummuliative Number

Sat, 31 Aug 2013 23:19:12 GMT

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

Re: Using Peek to get a Cummuliative Number

zagzebski — Sat, 31 Aug 2013 23:48:42 GMT

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.

Re: Using Peek to get a Cummuliative Number

Sun, 01 Sep 2013 02:32:07 GMT

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