Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2017-04-13 05:55 PM

Running total Using Peek.

Need some help with a weekly running total. for some reason my Formula keeps returning weird Values for My 'HoursRunningTotalWeek'.  I'd like to have the weekly total add up per employee.  There are many other employees but here is the data for one, Please see below. 





LABOR_TICKET:
LOAD * INLINE [

EMPLOYEE_ID , SHIFT_DATE , Week#, Flag_Weekday, CLOCK_IN , HOURS_WORKED

101, 42828 , 15 , 1 , 42828.20833 , 0.4

101, 42828 , 15 , 1 , 42828.22498 , 2.62

101, 42828 , 15 , 1 , 42828.33359 , 1.84

101, 42828 , 15 , 1 , 42828.43066 , 6.17

101, 42829 , 15 , 1 , 42829.19749 , 0.79

101, 42829 , 15 , 1 , 42829.23049 , 4.34

101, 42829 , 15 , 1 , 42829.43214 , 6.63

101, 42830 , 15 , 1 , 42830.20833 , 0.39

101, 42830 , 15 , 1 , 42830.22479 , 11.11

101, 42831 , 15 , 1 , 42831.19779 , 1.08

101, 42831 , 15 , 1 , 42831.24282 , 9.27

101, 42832 , 15 , 1 , 42832.20833 , 8.03

101, 42832 , 15 , 1 , 42832.2565 , 1.01

101, 42833 , 15 , 0 , 42833.19733 , 1.00

101, 42833 , 15 , 0 , 42833.21525 , 2.20

];

Labor_Ticket_Total:

LOAD*,

If(Match(Weekday(SHIFT_DATE),'Mon')>0,HOURS_WORKED,

     If(previous(EMPLOYEE_ID)=EMPLOYEE_ID and previous(Week#)=Week# and Flag_Weekday=1 ,

            HOURS_WORKED+Peek('HoursRunningTotal'))) as HoursRunningTotalWeek

Resident LABOR_TICKET

ORDER BY EMPLOYEE_ID ,

  SHIFT_DATE ,

  CLOCK_IN ;


Drop TABLE LABOR_TICKET;





Ideally I'd like to have the Labor_Ticket_Total table be represented as below:


EMPLOYEE_ID , SHIFT_DATE , CLOCK_IN , HOURS_WORKED, HoursRunningTotalWeek

128 , 42828 , 42828.20833 , 0.4, 0.4

128 , 42828 , 42828.22498 , 2.62, 3.02

128 , 42828 , 42828.33359 , 1.84, 4.86

128 , 42828 , 42828.43066 , 6.17, 11.03

128 , 42829 , 42829.19749 , 0.79, 11.82

128 , 42829 , 42829.23049 , 4.34, 16.16

128 , 42829 , 42829.43214 , 6.63, 22.79

128 , 42830 , 42830.20833 , 0.39, 23.18

128 , 42830 , 42830.22479 , 11.11, 34.29

128 , 42831 , 42831.19779 , 1.08, 35.37

128 , 42831 , 42831.24282 , 9.27, 44.64

128 , 42832 , 42832.20833 , 8.03, 52.67

128 , 42832 , 42832.2565 , 1.01, 53.68

128 , 42833 , 42833.19733 , 1, 0

128 , 42833 , 42833.21525 , 2.20, 0

];



1,823 Views
1 Solution

Accepted Solutions
swuehl
MVP
MVP
‎2017-04-14 05:35 AM

In response to Anonymous

Right, but I don't see the field 'HoursRunningTotal' you are trying to Peek() in your script. Does it exist?

I suggested to use the field 'HoursRunningTotalWeek', using this field should create your running total.

Also, use Rangesum() instead of the + operator to avoid issues when one of the operands is NULL.

Finally, I would use a different check for changed week or employee to reset the running total.

SET ThousandSep=',';

SET DecimalSep='.';

LABOR_TICKET:

LOAD * INLINE [

EMPLOYEE_ID , SHIFT_DATE , Week#, Flag_Weekday, CLOCK_IN , HOURS_WORKED

101, 42828 , 15 , 1 , 42828.20833 , 0.4

101, 42835 , 16 , 1 , 42835.20833 , 0.4

102, 42828 , 15 , 1 , 42828.20833 , 0.4

101, 42828 , 15 , 1 , 42828.22498 , 2.62

101, 42828 , 15 , 1 , 42828.33359 , 1.84

101, 42828 , 15 , 1 , 42828.43066 , 6.17

101, 42829 , 15 , 1 , 42829.19749 , 0.79

101, 42836 , 16 , 1 , 42836.19749 , 0.79

101, 42843 , 17 , 1 , 42843.19749 , 0.79

101, 42829 , 15 , 1 , 42829.23049 , 4.34

103, 42829 , 15 , 1 , 42829.23049 , 4.34

101, 42829 , 15 , 1 , 42829.43214 , 6.63

101, 42830 , 15 , 1 , 42830.20833 , 0.39

101, 42830 , 15 , 1 , 42830.22479 , 11.11

101, 42831 , 15 , 1 , 42831.19779 , 1.08

103, 42831 , 15 , 1 , 42831.19779 , 1.08

101, 42831 , 15 , 1 , 42831.24282 , 9.27

101, 42832 , 15 , 1 , 42832.20833 , 8.03

101, 42832 , 15 , 1 , 42832.2565 , 1.01

101, 42833 , 15 , 0 , 42833.19733 , 1.00

101, 42833 , 15 , 0 , 42833.21525 , 2.20

];

Labor_Ticket_Total:

LOAD*, Weekday(SHIFT_DATE) as Weekday,

If(Week# <> Previous(Week#) or EMPLOYEE_ID <> previous(EMPLOYEE_ID),HOURS_WORKED,

     If( Flag_Weekday=1 ,

            Rangesum(HOURS_WORKED,Peek('HoursRunningTotalWeek')))) as HoursRunningTotalWeek

Resident LABOR_TICKET

ORDER BY EMPLOYEE_ID ,

  SHIFT_DATE ,

  CLOCK_IN ;

Drop TABLE LABOR_TICKET;

View solution in original post

comm257085.qvw
1,628 Views
4 Replies
swuehl
MVP
MVP
‎2017-04-13 06:11 PM

You probably want to peek() the created field name?

Peek('HoursRunningTotalWeek')

1,628 Views
0 Likes
Anonymous
Not applicable
‎2017-04-13 06:19 PM
Author

In response to swuehl

If(Match(Weekday(SHIFT_DATE),'Mon')>0,HOURS_WORKED,

     If(previous(EMPLOYEE_ID)=EMPLOYEE_ID and previous(Week#)=Week# and Flag_Weekday=1 ,

            HOURS_WORKED+Peek('HoursRunningTotal'))) as HoursRunningTotalWeek



This is the equation i have tried to use. 

1,628 Views
0 Likes
swuehl
MVP
MVP
‎2017-04-14 05:35 AM

In response to Anonymous

Right, but I don't see the field 'HoursRunningTotal' you are trying to Peek() in your script. Does it exist?

I suggested to use the field 'HoursRunningTotalWeek', using this field should create your running total.

Also, use Rangesum() instead of the + operator to avoid issues when one of the operands is NULL.

Finally, I would use a different check for changed week or employee to reset the running total.

SET ThousandSep=',';

SET DecimalSep='.';

LABOR_TICKET:

LOAD * INLINE [

EMPLOYEE_ID , SHIFT_DATE , Week#, Flag_Weekday, CLOCK_IN , HOURS_WORKED

101, 42828 , 15 , 1 , 42828.20833 , 0.4

101, 42835 , 16 , 1 , 42835.20833 , 0.4

102, 42828 , 15 , 1 , 42828.20833 , 0.4

101, 42828 , 15 , 1 , 42828.22498 , 2.62

101, 42828 , 15 , 1 , 42828.33359 , 1.84

101, 42828 , 15 , 1 , 42828.43066 , 6.17

101, 42829 , 15 , 1 , 42829.19749 , 0.79

101, 42836 , 16 , 1 , 42836.19749 , 0.79

101, 42843 , 17 , 1 , 42843.19749 , 0.79

101, 42829 , 15 , 1 , 42829.23049 , 4.34

103, 42829 , 15 , 1 , 42829.23049 , 4.34

101, 42829 , 15 , 1 , 42829.43214 , 6.63

101, 42830 , 15 , 1 , 42830.20833 , 0.39

101, 42830 , 15 , 1 , 42830.22479 , 11.11

101, 42831 , 15 , 1 , 42831.19779 , 1.08

103, 42831 , 15 , 1 , 42831.19779 , 1.08

101, 42831 , 15 , 1 , 42831.24282 , 9.27

101, 42832 , 15 , 1 , 42832.20833 , 8.03

101, 42832 , 15 , 1 , 42832.2565 , 1.01

101, 42833 , 15 , 0 , 42833.19733 , 1.00

101, 42833 , 15 , 0 , 42833.21525 , 2.20

];

Labor_Ticket_Total:

LOAD*, Weekday(SHIFT_DATE) as Weekday,

If(Week# <> Previous(Week#) or EMPLOYEE_ID <> previous(EMPLOYEE_ID),HOURS_WORKED,

     If( Flag_Weekday=1 ,

            Rangesum(HOURS_WORKED,Peek('HoursRunningTotalWeek')))) as HoursRunningTotalWeek

Resident LABOR_TICKET

ORDER BY EMPLOYEE_ID ,

  SHIFT_DATE ,

  CLOCK_IN ;

Drop TABLE LABOR_TICKET;

comm257085.qvw
1,629 Views
Anonymous
Not applicable
‎2017-04-14 09:42 AM
Author

In response to swuehl

Thank you.  I was peeking the Wrong Field.  been spending so much time on this I missed that.

Much appreciated. 

1,628 Views
0 Likes