Aggregate Function

[Kully121]

Hi,

I'm trying to get an average of 3 weeks (all number fields) but keep getting the group by aggregate function error

Formula as below

Week Four: IIf([Week 4] Is Null,0,Avg([Week 1]+[Week 2]+[Week 3]))

Any ideas?

Cheers,

Kully121

 

[Andrew Fergus]

Can you post the full SQL for this query? Click View -> SQL View to get to it.
Andrew

 

[Kully121]

SELECT [A - Pubfolio Volumes].[TPD Product Code Calendar day], [A - Pubfolio Volumes].[Week 1], [A - Pubfolio Volumes].[Week 2], [A - Pubfolio Volumes].[Week 3], IIf([Week 4] Is Null,0,Avg([Week 1]+[Week 2]+[Week 3])) AS [Week Four]
FROM [A - Pubfolio Volumes]
GROUP BY [A - Pubfolio Volumes].[TPD Product Code Calendar day], [A - Pubfolio Volumes].[Week 1], [A - Pubfolio Volumes].[Week 2], [A - Pubfolio Volumes].[Week 3];

 

[Andrew Fergus]

Hi Kully, try this :

SELECT [A - Pubfolio Volumes].[TPD Product Code Calendar day], Sum([A - Pubfolio Volumes].[Week 1]) AS [SumOfWeek 1], Sum([A - Pubfolio Volumes].[Week 2]) AS [SumOfWeek 2], Sum([A - Pubfolio Volumes].[Week 3]) AS [SumOfWeek 3], Sum([A - Pubfolio Volumes].[Week 4]) AS [SumOfWeek 4], IIf(Sum([Week 4]) Is Null,0,(Sum([Week 1])+Sum([Week 2])+Sum([Week 3]))/3) AS [Week Four]
FROM [A - Pubfolio Volumes]
GROUP BY [A - Pubfolio Volumes].[TPD Product Code Calendar day];

HTH, Andrew

 

[Kully121]

Thanks works fine