I have created a BAQ where I am seeing how much of a product we have sold over the past couple of years, broken down into a weekly figure.
However, if we have not sold anything for that week, it does not return anything at all whereas I would like a 0 in its place, because it is not calculating the averages sales correctly.
Below is query I have so far. I believe I need to do something with ISNULL, but no matter what I try, I cannot get it to work.
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[Obsolete_c] as [Part_Obsolete_c],
[Part].[NonStock_c] as [Part_NonStock_c],
[Part].[InActive] as [Part_InActive],
(DATEADD(dd, -(DATEPART(dw, PartTran.TranDate)-1), PartTran.TranDate)) as [Calculated_WeekComm],
(Sum(PartTran.TranQty)) as [Calculated_TotalQty]
from Erp.Part as Part
inner join Erp.PartTran as PartTran on
Part.Company = PartTran.Company
and Part.PartNum = PartTran.PartNum
and ( PartTran.TranDate >= dateadd (week, -120, Constants.Today) and not PartTran.PONum > 0 )
where (Part.Company = 'DD01')
and (PartTran.TranType = 'DRP-CUS' or (PartTran.TranType = 'STK-CUS' or PartTran.TranType = 'STK-MTL' or PartTran.TranType = 'STK-KIT' ))
group by [Part].[PartNum],
(DATEADD(dd, -(DATEPART(dw, PartTran.TranDate)-1), PartTran.TranDate))
order by Part.PartNum, WeekComm
probably need to make the join to the PartTran as an OUTER join. An Inner join tells the query that the data must exist in the other table… but the outer join should still give you a value of zero (i think).
Personally, I’d look at InvcDtl instead of PartTran. PartTran tells you the product was shipped to the customer, but that doesn’t mean you sold it. AR shows what you actually billed to the customer, which is usually the closest definition to what people think of as “sold”. Not just ordered (short/over ships happen). Not just what pieces moved out of the warehouse (RMA’s, samples, etc. are a thing).
Also the correct syntax of ISNULL would be
Just make sure the field is actually null, which because you have no outer joins, is unlikely to be the case here. Also, if this is the entirety of your query, when you have zero sales for a week, the week will be missing regardless of your aggregate settings. You need to generate a list of dates or weeks and then outer join the ERP tables to see if there’s any corresponding records in that bucket.
As to how you’d get a list of dates, I’ve never done that in a BAQ personally. I usually script up a loop in SQL. You could probably set up a UserCode, fill it with dates, and join on that. I wouldn’t burn a whole UD table on it.