Total Sales YTD by Customer

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?

1 Like

Just have the customer name and the calculated field

If I remove the group by, then it ends up looking like this:

Remove the “Over (Partition By…” in the calculated field so that it just reads “Sum(InvcHead.InvoiceAmt)”

You then want to mark the Customer.Name field as Group By. You should only use either the window function or the group by on another field.

You could do this with a Pivot query.

Create a new query (Sub Query 1). Leave blank for now
Add another Sub Query (Sub Query 2)
Add the following tables

image

Link Customer to InvHead on Company and Customer Number
Link InvcHead to InvcDtl on Company and Invoice number

Add the following fields
image

Calculated Fields
InvMonth DatePart(Month,InvcHead.InvoiceDate)
InvYear DatePart(Year,InvcHead.InvoiceDate)
YearTotal Sum(InvcDtl.ExtPrice)OVER(PARTITION BY Customer.CustID,InvYear)

Go back to Sub Query 1 and add Sub Query 2
image

Right click on Sub Query 2 and set Pivot
image

Set the Pivot field to InvMonth
Add Months 01 thru 12 (Need to have the leading 0)
image

Set your Aggregate Formula to InvDtl_ExtPrice
image

Add your field to Sub Query 1
image

Run your Query

3 Likes

This worked! I tried this before and it didn’t, not sure why, maybe it just needed to refresh. Either way it’s working now. Thank you!

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.

I followed your steps and I got it. Thank you so much!!!

Would you mind sharing your BAQ please?

Hi, Here is the BAQ

SOLUT_YTD_ByCust.baq (30.1 KB)

1 Like

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

Here is the original query from 10.2.200
CustomerSales-4.baq (27.0 KB)

Load that into a dashboard or in this case linked to customer tracker and you should get something like this.

1 Like

Question - but can you walk through the steps for the Calculated Fields? I keep getting an error when I check syntax:

I don’t think that error is coming from your calculated field. Do you have any criteria on any of the tables?

image

Or do you have your Pivot already defined?

image

1 Like

If your top (SubQuery1) has no tables you’ll get this error. Just follow the instructions from post 6 above (Total Sales YTD by Customer - #6 by E102016) until the end.

Also, you may want to consider excluding Credit Memos in SubQuery2.

1 Like

No, to both of these. Do I need to define the Pivot first?

Appreciated.

Tried following along with the instructions, but it does not look like criteria was selected for any tables.

Ah, right… SubQuery1 has no tables added to it yet. Appreciate the insight. Still new-ish to BAQ building

1 Like