Grouping AVG, want to ignore calculation question.

[McArthurGDM]

Say that I have a table of values that I want to group based on Quarter, MetricID, and State. In table one, you'll see part of the original table without the grouping. Notice how there is a value missing for Q2 under the Quarter column...

When I group and do the average, as you see in the table below, you'll notice that the grouping still takes an average despite a value missing. Is there a way to get it so that it does not do the average if a value is missing in that particular grouping?



Thanks!

Quarter	MetricID	State	Values	Years
Q1	A1	TX	2463428	2015
Q1	A1	TX	2460958	2015
Q1	A1	TX	2463293	2015
Q2	A1	TX	2408239	2015
Q2	A1	TX		2015
Q2	A1	TX	2406541	2015
Q3	A1	TX		2015
Q3	A1	TX		2015
Q3	A1	TX		2015
Q4	A1	TX		2015
Q4	A1	TX		2015
Q4	A1	TX		2015

Quarter	MetricID	STATE	AvgOfValues	Years
Q1	A1	TX	2462559.66666667	2015
Q2	A1	TX	2407390	2015
Q3	A1	TX		2015
Q4	A1	TX		2015

[June7]

So there must be 3 values before you want an average calculated?

This requires a count of the values for each year/quarter/metricID/state combination. This count can be obtained with a DCount() domain aggregate or with an intermediate query that calcs the count. The count will be the condition that determines if average is calculated.

Query1
SELECT Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Count(Table1.Values) AS CountOfValues
FROM Table1
GROUP BY Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State;

Query2
SELECT Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Avg(IIf([CountOfValues]=3,[Values],Null)) AS AvgVals, Query1.CountOfValues
FROM Query1 INNER JOIN Table1 ON (Query1.State = Table1.State) AND (Query1.MetricID = Table1.MetricID) AND (Query1.Quarter = Table1.Quarter) AND (Query1.Years = Table1.Years)
GROUP BY Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Query1.CountOfValues;

All in one
SELECT Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Avg(IIf([CountOfValues]=3,[Values],Null)) AS AvgVals, Query1.CountOfValues
FROM (SELECT Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Count(Table1.Values) AS CountOfValues FROM Table1 GROUP BY Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State) AS Query1
INNER JOIN Table1 ON (Query1.State = Table1.State) AND (Query1.MetricID = Table1.MetricID) AND (Query1.Quarter = Table1.Quarter) AND (Query1.Years = Table1.Years)
GROUP BY Table1.Years, Table1.Quarter, Table1.MetricID, Table1.State, Query1.CountOfValues;

[McArthurGDM]

Thanks,

I knew a count had to be involved in some way. I tried a distinct count function with some variations but it wasn't working right. I appreciate the help!

[June7]

An alternative would be to just do one simple aggregate query that does the Avg() and Count() calcs then in a report have conditional expression for display of the average value.

=IIf([CountOfValues]=3, [AvgOfValues], Null)

[McArthurGDM]

Wow,

You were absolutely right. Just doing the count of the values within the aggregate query and then setting the report criteria worked and was way simpler. I can't believe I didn't see that.

Thanks!

[June7]

And if you wanted to show detail info, base report on the raw data and do the aggregate calcs in group and report footers.