6 Replies Latest reply on Feb 23, 2016 5:02 PM by pooja.gandhi

# Mixed Calculation field and integer

Hello,

I am trying to calculate FTE counts and I am having an issue with the results aggregating incorrectly.

Currently my data comes in at 5 levels

1. Facility
2. Cost Center
3. Job Class
4. Pay Type
5. Pay Period

The Sum of a cost centers hours would be the sum of the hours in each level below. I would then take that sum and divide by 80 to get the count of FTE's that were included for that Pay Period. Then if I remove the Pay Period Pill, This number should represent the Sum of Hours across all Pay Periods / (80 * # of Pay Periods).

My formula currently is sum(hours)/sum(80). This gives me the correct numbers for the pay period but incorrect for the year. How can i hold the 80 steady for the pay period and aggregate the rest properly?

Thank you!

• ###### 1. Re: Mixed Calculation field and integer

Harnish - Maybe try removing the SUM aggregation for the denominator. 80 should be a constant I am assuming, so only sum(hours)/80 should work. If not, I suggest attaching some sample rows of your dataset.

• ###### 2. Re: Mixed Calculation field and integer

Not sure I understand your request correctly, but tried to create something.

[Count pay period]

count([5. Pay Period])

[Hours/Pay period]

sum([Hours])/([Count pay period])

Thanks,

Shin

9.0 attacched.

• ###### 3. Re: Mixed Calculation field and integer

That is along the right lines.

I've attached screen shots here.

The calculation I used is 'sum(2014 Hours)/max(80)'

This is because I can not mix an aggregate and non-aggregate function.

the first screen shot is by pay period and the second is aggregated for the year.

• ###### 4. Re: Mixed Calculation field and integer

Based on the screenshots your results are correct for both the scenarios. Based on what you want though, you may want to try:

SUM(2014 Hours)/(80*COUNTD(Pay Period))

This should work for both scenarios, because when pay period is in the view countd(pay period) should be 1 and hence the division by 80. When it is not in the view, it would be number of pay periods in that year. Hope that works.

1 of 1 people found this helpful
• ###### 5. Re: Mixed Calculation field and integer

That's correct, Pooja, Thank you!

• ###### 6. Re: Mixed Calculation field and integer

Great, please close the thread by marking my answer as correct for others to see it when they search for a similar topic. Glad that helped!