Set Analysis Expression

[billuran]

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!!

[Digvijay_Singh · Accepted solution]

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

[swuehl]

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

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

[billuran]

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

[billuran]

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.

[Digvijay_Singh]

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

[billuran]

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