I’m trying to create a BAQ that will give me YTD customer sales by customer name. I am stumped however currently. I created it using ERP.Customer and ERP.InvHead. Then I created table criteria based upon parameter for start and end dates which must be input upon running it. For the display fields, I have Customer_Name of which I selected for Group By, then I created a calculated field
Yet I get the error message
"Column “ERP.InvcHead.InvoiceAmt” is invalid in the select list because it is not contained in either an aggregate function or the Group BY clause.
If I click the group by for the calculated field, nothing works.
If I remove the group by for the Customer Name, it works, but will basically total the YTD on each invoice, creating multiple customer lines with the same YTD total. How do I fix this?
When using a Windowing function you probably don’t need any Group By’s.
If you want the YTD total per customer, you can’t have the Invoice Number in the select list.
Can you provide a screenshot of the fields you have selected to Display?
to do this you really need to create the calculated YTD value via a Sub Query. Getting this kind of matrix in regular BAQ is hard, this is exactly what EDA does out of the box.
Shoot, I’m on version 10.2.300.0 and therefore I cannot import that BAQ file. Is it possible you could paste the SQL code that is in the General tab when that BAQ is open? Again, thanks for all your help!
select
[SubQuery2].[Customer_CustID] as [Customer_CustID],
[SubQuery2].[Customer_Name] as [Customer_Name],
[SubQuery2].[Calculated_InvYear] as [Calculated_InvYear],
[SubQuery2].[Calculated_YearTotal] as [Calculated_YearTotal]
from (select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcDtl].[ExtPrice] as [InvcDtl_ExtPrice],
(DatePart(Month,InvcHead.InvoiceDate)) as [Calculated_InvMonth],
(DatePart(Year,InvcHead.InvoiceDate)) as [Calculated_InvYear],
(Sum(InvcDtl.ExtPrice)OVER(PARTITION BY Customer.CustID,InvYear)) as [Calculated_YearTotal]
from Erp.Customer as Customer
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
and Customer.CustNum = InvcHead.CustNum
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum) SubQuery2_src
pivot
(sum(InvcDtl_ExtPrice)
for Calculated_InvMonth in (‘01’, ‘02’, ‘03’, ‘04’, ‘05’, ‘06’, ‘07’, ‘08’, ‘09’, ‘10’, ‘11’, ‘12’) )
as SubQuery2
