Average on date only not record?

[jillnoble]

Thanks again, As you've stuck with me this far maybe you could take a look. I basically used the report wizard, bunged everything onto the report and grouped on Employee, Year and Week just to see but if you look at week 15 for example, its still included that week even though there's only 4 days in it?

 

[MajP]

So for week 15 they worked 5 days but one day was on 2018. If the rule is that a full week cannot include job 2018 days then in the very first query qryDailyPay filter out 2018. Job <> 2018

The issue is that you are counting work days first. Then you filter the records out not in 2018. The days were previously calculated not excluding 2018. So you have to filter out first.

 

[jillnoble]

Thank you. Sorry for not saying thank you earlier but I've been trying to sort this on my own but I just can't get it. I've now noticed that when I go back to your latest qry (qryFullWeeksWorked_Not_2018) it stops at wk 42? and when I added the filter you suggest above it stops at 31? I tried stripping it right back and adding one filter at a time it seems to be the >4 filer upsetting things, does that sound right? Also, you have DaysWorked twice in your qry with one of them having a title of Expr1003?

 

[MajP]

Also, you have DaysWorked twice in your qry with one of them having a title of Expr1003?

If you add a field by mistake more than once it gives the second one a name alias, so that you do not have the same name twice. That is a mistake.
See if this report is correct. I did not double check but do you believe there are full weeks (excluding 2018) beyond week 31? If so can you point out a week so I can double check. If not this is what I get.

 

[jillnoble]

The report is still showing 2018? The 'qryAvgDailyPerWeek_Not2018' looks good, but the final qry, which filters out >4 then has 2018 included? and weeks 38, 39, 40 for example, are on the AvgDaily qry but not the final qry. I'm confused!

 

[MajP]

Got it thanks.

 

[MajP]

Dumb mistake in last query. Try now

 

[jillnoble]

Thank you, that's brilliant, but dare I say it (you're gonna wish I never joined this forum!), still not showing any details after wk 31, even though it does on the Avg qry??

 

[Poppa Smurf]

I would like to add one general comment regarding averaging. You can not Average and Average.
This is mathematically incorrect
I would think in the week footer
=Avg([basic] + [Overtime])
in the employee footer
=Avg(Avg([basic] + [Overtime]))

Personally I would total the Employee's weekly payments for the year then divide by 52 to determine the average weekly payment.

The employee's weekly payment is part of the pay section duties and each weekly payment will be adjusted by the pay section as per your guidelines.

Why is Overtime counted? Is this part of the employee's working condition when determining holiday payment. Normally the employee's basic salary per week is used to determine the total holiday payment.

 

[jillnoble]

Hi Poppa Smurf, thank you for joining! Was so worried I was putting it all on MajP! I like what you're thinking, Basic pay and Overtime (Not bonus, needs to be the average, MajP, don't worry, didn't like to say as you've helped me sooo much, was just gonna take Bonus out of the equation) Yes, an employee's overtime counts (Not bonus), Gov law states that employees will be paid their holiday (inc Overtime), of a daily average over 52 WORKING weeks, for every day off from Jan 2021. Hence why I'm trying to create this report.

 

[jillnoble]

I've added another employee if that helps?

 

[MajP]

Personally I would total the Employee's weekly payments for the year then divide by 52 to determine the average weekly payment

You cannot do that AFAIK. I been told you only count weeks paid, that are full weeks (five or more days) and that exclude project 2018 pay. Not sure if that rule makes sense but that was the rule I was told.

 

[MajP]

still not showing any details after wk 31, even though it does on the Avg qry??

If you look at that query there are no weeks worked on 2017 with 5 or more days. That query does not filter on days worked. You can look at the days worked. So the report and query match. Do you see a week after 31 in the data with 5 days worked on 2017?

If I filter the query on days worked these are the only weeks with 5 days on 2017
qryAvgDailyPerWeek_Not2018 qryAvgDailyPerWeek_Not2018

Job	Employee	YearNumber	WeekNumber	DaysWorked
2017	Noble Jill	2020​	11​	5​
2017	Noble Jill	2020​	14​	5​
2017	Noble Jill	2020​	17​	5​
2017	Noble Jill	2020​	18​	5​
2017	Noble Jill	2020​	20​	5​
2017	Noble Jill	2020​	21​	5​
2017	Noble Jill	2020​	23​	5​
2017	Noble Jill	2020​	24​	5​
2017	Noble Jill	2020​	25​	5​
2017	Noble Jill	2020​	26​	5​
2017	Noble Jill	2020​	27​	5​
2017	Noble Jill	2020​	31​	5​

So if you want the average full weeks worked not on 2018, then you need the pay for (11+14+17...31) divided by 12 weeks.

 

[jillnoble]

Hi, Don't understand what AFAIK is? I've taken a screen shot of what I get from the 'qryAvgDailyPerWeek_Not2018' and it includes days/weeks after wk 31? Think I've solved it with this? Although now I need it to total the averages but not all of the averages, just the weeks. If I try to total it gives me the weeks added up as records not actual number of weeks. I need the daily average over 52 weeks so I want to total the number of weeks so I can then keep going back to run the report until it gives me a total of 52 full weeks worked.

 

[MajP]

I see what I did. In the very first query when editing I hit a Sum and turned it into a group by. Must have did that by accident in one of the iterations. That trickled down through all the reports. See if this is what you want and then describe what you want in the grand avg.

This report show all non 2018 paydays for weeks with 5 or more days worked excluding 2018 days. The avg is the weekly avg by taking the sum dividing by the days worked. I moved the avg and totals into the footer to make more sense.

 

[Poppa Smurf]

You cannot do that AFAIK. I been told you only count weeks paid, that are full weeks (five or more days) and that exclude project 2018 pay. Not sure if that rule makes sense but that was the rule I was told.

Now is this weekly payment before tax and any pre-tax deductions are made?

Assume an employee is paid each week regardless of the number of days worked. From this weekly payment you will know the base pay and the overtime worked for that week.

Using the extracted data create a query that lists all weekly payment where the number of days are equal to or greater than the criteria in this case 5. Now using the same query calculate the average of the weekly payment.

If you continue using the average of an average you will calculate the incorrect amount which will result in underpayments.

 

[MajP]

If you continue using the average of an average you will calculate the incorrect amount which will result in underpayments.

To be clear in no place is there an average of the average.
The final report is based on this query.
qryWeeklyPay_Not2018_5orMore
qryAvgDailyPerWeek_Not2018_50rMore
The averages are the sum of the pay per week divided by the number of days worked. Only including weeks for 2017 and weeks where the days worked are greater than 4.

To get the average daily pay dor this subset (if that is what is wanted) then simply use another aggregate to sum total pay / Sum days worked

So the yearl daily average is done by summing the yearly pay and for each category and dividing by the total days worked for weeks with 5 or more days.
qryYearlyDailyAvg_No2018_5orMoreDays qryYearlyDailyAvg_No2018_5orMoreDays

Job	Employee	YearNumber	DaysWorkedPerYear	YearlyBasic	YearlyBasicDailyAvg	YearlyOvertime	YearlyOvertimeDailyAvg	YearlyBonusPay	YearlyBonusDailyAvg	YearlyTotalPay	YearlyTotalDailyAvg
2017	Noble Jill	2020​	157​	$11,379.62​	$72.48​	$505.00​	$3.22​	$1,096.20​	$6.98​	$12,980.82​	82.6803821656051​

 

[MajP]

The above query was based on the weekly sums.
qryWeeklyPay_Not2018_5orMore qryWeeklyPay_Not2018_5orMore

Job	YearNumber	WeekNumber	Employee	DaysWorked	WeeklyBasicPay	WeeklyOvertimePay	WeeklyBonusPay	WeeklyTotalPay
2017	2020​	3​	Noble Jill	5​	$569.14​	$0.00​	$0.00​	$569.14​
2017	2020​	4​	Noble Jill	5​	$565.52​	$0.00​	$0.00​	$565.52​
2017	2020​	6​	Noble Jill	5​	$565.52​	$0.00​	$0.00​	$565.52​
2017	2020​	9​	Noble Jill	5​	$565.52​	$0.00​	$0.00​	$565.52​
2017	2020​	11​	Noble Jill	5​	$580.00​	$0.00​	$0.00​	$580.00​
2017	2020​	13​	Noble Jill	5​	$551.00​	$0.00​	$0.00​	$551.00​
2017	2020​	14​	Noble Jill	5​	$507.50​	$0.00​	$0.00​	$507.50​
2017	2020​	17​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	18​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	20​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	21​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	23​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	24​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	25​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	26​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	27​	Noble Jill	5​	$0.00​	$0.00​	$101.50​	$101.50​
2017	2020​	31​	Noble Jill	5​	$466.90​	$0.00​	$40.60​	$507.50​
2017	2020​	32​	Noble Jill	5​	$406.00​	$0.00​	$20.30​	$426.30​
2017	2020​	33​	Noble Jill	5​	$406.00​	$0.00​	$20.30​	$426.30​
2017	2020​	34​	Noble Jill	5​	$406.00​	$0.00​	$20.30​	$426.30​
2017	2020​	35​	Noble Jill	5​	$406.00​	$0.00​	$20.30​	$426.30​
2017	2020​	38​	Noble Jill	5​	$590.89​	$0.00​	$0.00​	$590.89​
2017	2020​	39​	Noble Jill	5​	$551.02​	$0.00​	$0.00​	$551.02​
2017	2020​	40​	Noble Jill	5​	$565.51​	$0.00​	$0.00​	$565.51​
2017	2020​	41​	Noble Jill	5​	$203.00​	$0.00​	$60.90​	$263.90​
2017	2020​	43​	Noble Jill	5​	$580.00​	$0.00​	$0.00​	$580.00​
2017	2020​	44​	Noble Jill	5​	$590.89​	$0.00​	$0.00​	$590.89​
2017	2020​	46​	Noble Jill	5​	$561.88​	$0.00​	$0.00​	$561.88​
2017	2020​	47​	Noble Jill	5​	$568.83​	$0.00​	$0.00​	$568.83​
2017	2020​	49​	Noble Jill	6​	$642.50​	$345.00​	$0.00​	$987.50​
2017	2020​	50​	Noble Jill	6​	$530.00​	$160.00​	$0.00​	$690.00​

 

[jillnoble]

Hi MajP Thats brilliant! Thank you so much for ALL your help! That works so well! I just can't say thank you enough!!!

 

[MajP]

No problem. Hope that is answering the right question for you.