Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

blunckc1
New Contributor II

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
Tags (1)
1 Solution

Accepted Solutions
kkkumar82
Valued Contributor III

Re: Expression help

Greater than 5.png

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

3 Replies
kkkumar82
Valued Contributor III

Re: Expression help

Greater than 5.png

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

blunckc1
New Contributor II

Re: Expression help

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
Valued Contributor III

Re: Expression help

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.