Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

billuran
Contributor

Set Analysis Expression

Hello All,

Need some help on an expression. I am converting labor hours into an increments of FTEs(Full Time Equivalents) . Break down of logic:

FTE= Worked Hours/(# of Pay Periods Worked*80)

Here is the predicament.

I have a dashboard that filters for pay period 1-5= (5 total)

and shows two employees in table:

Employee A:works Pay period 1-5 and works 400 hours= So FTE=400/(5 PPs*80)=1

Employee B: Worked PP 2-3 and worked 160 hours= So FTE=160/( 5 PPs *80=.4

However if I select employee B only, the new calculation assumes that only 2 pay periods are selected because Employee B only shows up in 2 Pay periods and makes them a 1.0 FTE.

I want an expression that looks at the total PP filter date and counts these number of pay periods regardless of how many PP an employee shows up.

Here is my expression as of now

sum(Hours)/(((max(PPDate)-min(PPDate))/14)+1)*26/2080 The red portion is how I count the number of pay periods selected and the green is my annualizing the hours to divide by 20180 instead of 80.


Thankyou for your help on this!!

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Set Analysis Expression

Not sure but looks like you want o disregard all selection while calculating Max()-Min() in your formula, may be like this -

sum(Hours)/(((max({1}PPDate)-min({1}PPDate))/14)+1)*26/2080

6 Replies
Digvijay_Singh
Honored Contributor III

Re: Set Analysis Expression

Not sure but looks like you want o disregard all selection while calculating Max()-Min() in your formula, may be like this -

sum(Hours)/(((max({1}PPDate)-min({1}PPDate))/14)+1)*26/2080

MVP
MVP

Re: Set Analysis Expression

Or if you just need to ignore selections in Employee field:

sum(Hours)/(((max({<EmployeeField= >}PPDate)-min({<EmployeeField= >}PPDate))/14)+1)*26/2080

billuran
Contributor

Re: Set Analysis Expression

Yes but i want to be able to filter by Date still.

billuran
Contributor

Re: Set Analysis Expression

Thanks for the feedback here is what it is generating in the test column. The other column should be what it should be. Again the Min and Max dates are being counted at the dimension level. If there is a better way to count the PP filtered i am all ears.

qlik.PNG

Digvijay_Singh
Honored Contributor III

Re: Set Analysis Expression

Then you should go with what Stefan's suggested and mark his response as the correct one. Thanks.

billuran
Contributor

Re: Set Analysis Expression

I was able to maybe figure it using the Total function.

sum({<[Exclude Hours]={'NO'},[Pay Type]={'Productive'}>} Hours)/(((max(TOTAL{<Group=>}MaxPP)-min(TOTAL{<Group= >}MaxPP))/14)+1)*26/2080

Community Browser