I’ve created a BAQ to pull the total of customer purchases on my first subquery and summed them up by the month on the second sub. The 3rd subquery is the pivot, which I created calculated columns on, telling it to place a ‘0.00’ on any null cell.
Then on my top level query, I pulled these calculated columns from the pivot into a single column. That way each year and month have a value, regardless if there are no records for that customer. No records = “0.00”. I plan on creating a 3, 6 and 12 month trailing total in another 3 columns so I need a value for each month of the year.
Okay, so now the problem. When I try to save or test the query I get this error:
Here’s the BAQ Phrase:
select distinct
[PivotData].[Customer_CustID] as [Customer_CustID],
[PivotData].[Calculated_Year] as [Calculated_Year],
(FiscalPer.FiscalPeriod) as [Calculated_Month],
(case
when PivotData.Calculated_Pivot_1 is null then '0.00'
when PivotData.Calculated_Pivot_2 is null then '0.00'
when PivotData.Calculated_Pivot_3 is null then '0.00'
when PivotData.Calculated_Pivot_4 is null then '0.00'
when PivotData.Calculated_Pivot_5 is null then '0.00'
when PivotData.Calculated_Pivot_6 is null then '0.00'
when PivotData.Calculated_Pivot_7 is null then '0.00'
when PivotData.Calculated_Pivot_8 is null then '0.00'
when PivotData.Calculated_Pivot_9 is null then '0.00'
when PivotData.Calculated_Pivot_10 is null then '0.00'
when PivotData.Calculated_Pivot_11 is null then '0.00'
when PivotData.Calculated_Pivot_12 is null then '0.00'
when Month = 1 then PivotData.Calculated_Pivot_1
when Month = 2 then PivotData.Calculated_Pivot_2
when Month = 3 then PivotData.Calculated_Pivot_3
when Month = 4 then PivotData.Calculated_Pivot_4
when Month = 5 then PivotData.Calculated_Pivot_5
when Month = 6 then PivotData.Calculated_Pivot_6
when Month = 7 then PivotData.Calculated_Pivot_7
when Month = 8 then PivotData.Calculated_Pivot_8
when Month = 9 then PivotData.Calculated_Pivot_9
when Month = 10 then PivotData.Calculated_Pivot_10
when Month = 11 then PivotData.Calculated_Pivot_11
when Month = 12 then PivotData.Calculated_Pivot_12
else null end) as [Calculated_OrderAmt]
from (select distinct
[Pivot].[OrderHed_Company] as [OrderHed_Company],
[Pivot].[Customer_CustID] as [Customer_CustID],
[Pivot].[Customer_Name] as [Customer_Name],
[Pivot].[Calculated_Year] as [Calculated_Year],
(case when Pivot.1 is null then '0.00' else Pivot.1 end) as [Calculated_Pivot_1],
(case when Pivot.2 is null then '0.00' else Pivot.2 end) as [Calculated_Pivot_2],
(case when Pivot.3 is null then '0.00' else Pivot.3 end) as [Calculated_Pivot_3],
(case when Pivot.4 is null then '0.00' else Pivot.4 end) as [Calculated_Pivot_4],
(case when Pivot.5 is null then '0.00' else Pivot.5 end) as [Calculated_Pivot_5],
(case when Pivot.6 is null then '0.00' else Pivot.6 end) as [Calculated_Pivot_6],
(case when Pivot.7 is null then '0.00' else Pivot.7 end) as [Calculated_Pivot_7],
(case when Pivot.8 is null then '0.00' else Pivot.8 end) as [Calculated_Pivot_8],
(case when Pivot.9 is null then '0.00' else Pivot.9 end) as [Calculated_Pivot_9],
(case when Pivot.10 is null then '0.00' else Pivot.10 end) as [Calculated_Pivot_10],
(case when Pivot.11 is null then '0.00' else Pivot.11 end) as [Calculated_Pivot_11],
(case when Pivot.12 is null then '0.00' else Pivot.12 end) as [Calculated_Pivot_12]
from (select distinct
[Data].[Calculated_Year] as [Calculated_Year],
[Data].[Calculated_Month] as [Calculated_Month],
[Data].[OrderHed_Company] as [OrderHed_Company],
[Data].[Customer_CustID] as [Customer_CustID],
[Data].[Customer_Name] as [Customer_Name],
(sum(Data.OrderHed_DocOrderAmt) over(partition by Data.OrderHed_Company, Data.Customer_CustID, Data.Calculated_Year, Data.Calculated_Month)) as [Calculated_MonthRevenue]
from (select distinct
[OrderHed].[Company] as [OrderHed_Company],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
(datepart(year, OrderHed.OrderDate)) as [Calculated_Year],
(datepart(month, OrderHed.OrderDate)) as [Calculated_Month]
from Erp.Customer as Customer
inner join Erp.OrderHed as OrderHed on
Customer.Company = OrderHed.Company
and Customer.CustNum = OrderHed.BTCustNum
and ( OrderHed.OrderDate >= dateadd(yyyy, -4, Constants.Today) )) as Data) Pivot_src
pivot
(sum(Calculated_MonthRevenue)
for Calculated_Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )
as Pivot) as PivotData
inner join Erp.FiscalPer as FiscalPer on
PivotData.Calculated_Year = FiscalPer.FiscalYear
Even though I get an error it still runs and will give me the correct data when I test it, which is super frustrating… It’s my first BAQ with a Pivot so I appreciate any help.