By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Weighted YTD Avg?

P: n/a
I have the following data
Month/Year Rep Avg Courtesy Rating Number Returned
--------------------------------------------------------
May 2005 BJM 3.6 2
June 2005 BJM 3.2 3

I want to have a YTD weighted average -

So in this case, here is how I would calculate that:

Sum(Avg Rating * Number Returned)
---------------------------------
Total Number Returned

3.6*2+3.2*3 / 5 = YTD Total of 3.36

How can this be done in a query or report?

Thanks
Brian

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Brian,

Try this, I'm sure there are others who are better at SQL who
can show you a better method.

1.Create Query1
SELECT Table1.tRep, Year([tDate]) AS tYear, Sum(([tRate]*[tReturns])) AS
Total
FROM Table1 GROUP BY Table1.tRep, Year([tDate]);

This will give you the year extracted from the date so you can group be
rep id
and year. Also it calculates the sum of rate * returns which will be by
rep id and year.
2. Create Query2
SELECT Table1.tRep, Year([tDate]) AS tYear, Sum(Table1.tReturns) AS
SumOftReturns
FROM Table1 GROUP BY Table1.tRep, Year([tDate]);

This gives you the total number of returns by rep id and year.

3. Create Query3
SELECT Query1.tRep, Query1.tYear, Query1.Total, [Total]/[SumOftReturns]
AS WtAvg
FROM Query1 INNER JOIN Query2 ON (Query1.tYear = Query2.tYear) AND
(Query1.tRep = Query2.tRep);

This will join Query1 and Query2 by rep id and year and calculate the Wt
Avg by
Total / SumOftReturns

BerkshireGuy wrote:
I have the following data
Month/Year Rep Avg Courtesy Rating Number Returned
--------------------------------------------------------
May 2005 BJM 3.6 2
June 2005 BJM 3.2 3

I want to have a YTD weighted average -

So in this case, here is how I would calculate that:

Sum(Avg Rating * Number Returned)
---------------------------------
Total Number Returned

3.6*2+3.2*3 / 5 = YTD Total of 3.36

How can this be done in a query or report?

Thanks
Brian


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.