Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
blunckc1
Creator
Creator

Expression help

Hi community,

I want to calculate/identify the number of times an employee has performed more than X hours of overtime in a [PayPeriod#].

This be used to help identify individuals who are continually working excessive overtime hours.

X is a variable (vOTLimit) which can be changed through the navigation button extension.  Default value is 5 with variable definition simply written as 5.  No '' wrapping it or = before hand.  If this is wrong let me know also.

Value can be changed to 10,15, 20 or 25.

Overtime hours are calculated through the following set analysis expression which works =

Sum({$<[Time Category A]={'Worked'},[Payment Type Category]={'Overtime'}>}Hours)

This expression will be sitting in a tablebox chart which will simply have the individuals ID number and their name:

  

PIDPANEmployee NameNumber of pay periods overtime has been worked in excess of (vOTLimit) hours
XXXXXXBob0
AAAAAAJane2
BBBBBBPaul6

I tried using a combo of an if statement and an aggr expression but no luck:

Sum(Aggr(If(Sum({$<[Time Category A]={'Worked'},[Payment Type Category]={'Overtime'}>}Hours)>=(vOTLimit),1,0),distinct([PayPeriod#])))

Made the [PayPeriod#] distinct because the [PayPeriod#] is listed against every individual date overtime is worked.

Sample data:

    

PIDPANTime Category APayment Type CategoryStartTimeDatePayPeriod#sum(Hours)
XXXXXXWorkedOvertime22/04/2017PP22/20171.5
AAAAAAWorkedOvertime14/05/2016PP24/20166
AAAAAAWorkedOvertime28/05/2016PP25/20166
BBBBBBWorkedOvertime2/01/2016PP14/20164
BBBBBBWorkedOvertime3/01/2016PP14/20163.5
BBBBBBWorkedOvertime12/02/2016PP17/20162.5
BBBBBBWorkedOvertime13/02/2016PP17/20164.5
BBBBBBWorkedOvertime14/02/2016PP17/20164.5
BBBBBBWorkedOvertime15/02/2016PP18/20163
BBBBBBWorkedOvertime16/02/2016PP18/20162.5
BBBBBBWorkedOvertime17/02/2016PP18/20163
BBBBBBWorkedOvertime18/02/2016PP18/20163.5
BBBBBBWorkedOvertime18/03/2016PP20/20162.5
BBBBBBWorkedOvertime19/03/2016PP20/20163.5
BBBBBBWorkedOvertime20/03/2016PP20/20164
BBBBBBWorkedOvertime21/03/2016PP20/20162
BBBBBBWorkedOvertime22/03/2016PP20/20163.5
BBBBBBWorkedOvertime23/03/2016PP20/20161.5
BBBBBBWorkedOvertime24/03/2016PP20/20163.5
BBBBBBWorkedOvertime29/04/2016PP23/20161
BBBBBBWorkedOvertime30/04/2016PP23/20164.5
BBBBBBWorkedOvertime1/05/2016PP23/20163
BBBBBBWorkedOvertime3/05/2016PP23/20163
BBBBBBWorkedOvertime4/05/2016PP23/20161.5
BBBBBBWorkedOvertime5/05/2016PP23/20163.5
BBBBBBWorkedOvertime1/07/2016PP1/20172.5
BBBBBBWorkedOvertime2/07/2016PP1/20175.5
BBBBBBWorkedOvertime3/07/2016PP1/20176
BBBBBBWorkedOvertime4/07/2016PP2/20171.5
1 Solution

Accepted Solutions
kkkumar82
Specialist III
Specialist III

Greater than 5.png

I tried that in Qlikview, is that you want, for now I ignored set analysis

View solution in original post

3 Replies
kkkumar82
Specialist III
Specialist III

Greater than 5.png

I tried that in Qlikview, is that you want, for now I ignored set analysis

blunckc1
Creator
Creator
Author

That works!  Thank you.  So the aggr function literally creates a virtual table of data that you can then run other expressions over?

Interestingly though, this works in a KPI chart but it playing up in a straight table in Sense.  Not a worry though.

kkkumar82
Specialist III
Specialist III

yes, thats what picked correctly from Aggr , if you observe we don't have PayPeriod# dimension in the table but still we were able to do the aggregation using that also.