Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Peek function used like rangesum in script

Is it possible to use the peek function in the script similar to the rangesum(above()) function?

I have the following formula in a pivot and need to transfer the same functionality to the script.

if(rangesum(above(sum(if([Hours Code]='',[Paycor Hours])),0,RowNo()))<=40,0,

if(rangesum(above(sum(if([Hours Code]='',[Paycor Hours])),1,RowNo()))>40,

sum(if([Hours Code]='',[Paycor Hours])),

if(rangesum(above(sum(if([Hours Code]='',[Paycor Hours])),1,RowNo()))<=40,

rangesum(above(sum(if([Hours Code]='',[Paycor Hours])),0,RowNo()))-40))))

+

sum(if([Hours Code]='OT',[Paycor Hours]))

This formula is calculating Overtime over a week. I need the script to look at all the days of the previous week to current record, and then change the formula weather 40 hours has been reached. This needs to then be reset to start the process over for each new week. This will give me several fields based on each days specific row.

overtime

standard time

field standard time

field overtime

night premium

night premium overtime

night premium field overtime

night premium field overtime

Since the night premium is specific to the day that it was worked on, I need this function to calculate if at that time they had reach overtime or not. The final result is a summary for each employee, hence why I need this done on the script level

Tags (2)
1 Reply
whiteline
Honored Contributor II

Re: Peek function used like rangesum in script

Hi, Marc.

I would say like above, cause the rangesum is still useful in this case.

Of course, you have to preapare a table properly sorted and aggregated.

Then create a separete field for calculating rolling sum with peek -ing the same field, reseting the accumulation each time the new week starts.

Community Browser