Return 0 instead of blank field in BAQ for PartTran

[KHudson (Karen Hudson)]

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.

select 
	[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],
	[Part].[PartDescription],
	[Part].[Obsolete_c],
	[Part].[NonStock_c],
	[Part].[InActive],
	(DATEADD(dd, -(DATEPART(dw, PartTran.TranDate)-1), PartTran.TranDate))
order by Part.PartNum, WeekComm

Current output data

28/02/2021 - 2
07/03/2021 - 2
14/03/2021 - 5
21/03/2021 - 5
04/04/2021 - 10

I am missing 28/03/2021 where I would want it to be 0.

Can anybody help?

[jkane (John Kane)]

Welcome @KHudson. I would try.

COALESCE(Sum(PartTran.TranQty), 0)

And a friendly tip on this forum, put your code between 3 backticks (the key above Tab). So, ``` CODE ```.

Some people get grumpy if you don’t.

[timshuwy (Tim Shoemaker)]

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).

[KHudson (Karen Hudson)]

Thank you for the tip, I will remember in future.

[jtownsend (John Townsend)]

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

ISNULL(YourField, 0)

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.

[klincecum (Kevin Lincecum)]

@jtownsend

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.

I’d have a hard time explaining this on phone but you could hack something together with row_number probably.

relevant:

String_split in BAQ

To add on to @jdtrent’s comment… Make a sub that returns the top 100 rows(or whatever the max number of fields you expect) of a table that has at least that many rows (parttran always has lots of rows). It only needs 2 fields: Company (just something to link it to the next subquery) Calc field: Name: RowNum Type integer Expression: ROW_NUMBER() OVER(ORDER BY PartTran.TranNum) Now make a subquery with the table containing the field to split , and the above subquery. The Calc field wil…

[jtownsend (John Townsend)]

Yeah, that would totally work.

DATEADD(D, -ROW_NUMBER() OVER(ORDER BY PartTran.TranNum) +1, GETDATE())

Should start from today and count backwards for however many days you put into the TOP clause.

[klincecum (Kevin Lincecum)]

@jtownsend

Yeah, that would totally work.

I knew you wouldn’t let me down.