BAQ - How can I show null rows for a sum calculated field?

Kinetic ERP
,
1

I’m trying to create a table for analyzing part seasonality. I created 2 BAQ calculated fields to group by Year & Month, and another calculated sum to show monthly order qty. I need to include the months with 0’s, rather than having the BAQ’s SUM skip them.

How can I show the null rows?

I think I’m close, but here’s what didn’t work:

1 ○ COALESCE(sum(OrderDtl.OrderQty),0)
2 ○ ISNULL (sum (OrderDtl.OrderQty), 0)
3 ○ ISNULL (sum 
     (case 
        when OrderDtl.OrderQty > 0 then OrderDtl.OrderQty 
	else 0
    end),0)
4 ○ IF (ISNULL(sum(OrderDtl.OrderQty)), 0, sum(OrderDtl.OrderQty))

The first 3 pass the error check, and the last does not.
image

2

I always use the double i if statement. You also need a second parameter in the ISNULL. Something like this.
SQL Server ISNULL() Function (w3schools.com)

iif((ISNULL(sum(OrderDtl.OrderQty), 0)=0, 0, sum(OrderDtl.OrderQty))

I think you were trying to use ISNULL as the if condition. I just adjusted it a little so it should work.

3

Or maybe a case statement?
,(case
when ISNULL(sum(OrderDtl.OrderQty),0) = 0 then 0
else sum(OrderDtl.OrderQty)
end)
as Qty

1 Like
4

Hi Nate, I tried that, and it errors out with an extra comma somewhere. It seems like it needed and extra ) at the end, but that didn’t fix it either. can’t find the issue, though!
image

Respaced it for visual clarity and added the ), but still get that same error:
image

@bordway - Thanks. I gave it a try, but this didn’t add the 0 records either.
image

image
image636×520 24.9 KB

Thanks for trying. Seems like using ISNULL to add the 0 should work, with or without a case. Other’s have done it successfully in BAQ for calculations.

I tried adding a +1 to the formula, but that also doesn’t show the row, so maybe it’s not a null issue, and instead it’s a join issue. What if there’s no row to fill?

image

image

5 
SUM(ISNULL(OrderDtl.OrderQty, 0))

Then make your table a left join, currently its an inner join in screenshot above.

For Reference

Just for the sake of discussion, here is also a CASE Statement where I want to add the value if its a special type, otherwise remain at 0.

SUM(
 CASE WHEN PartTran.TranType = 'MFG-STK' OR PartTran.TranType = 'MFG-CUS' OR PartTran.TranType = 'MFG-WIP' THEN
 PartTran.TranQty
 ELSE
 0
 END)
1 Like
6

Thanks for jumping in, Haso. I tried the left join, but still no July 2019 with a 0.

image

I also tried the sum outside of the case, but no dice either. :frowning:

image

Still think it might be related to not having any data at all, so there is no row to start with. Maybe it’s due to the other GroupBy’s for the date columns? How would BAQ know to even create the ‘2019-07’ row if there is no order detail with that record to create that calculated field? Jeez, I don’t know where to start with that one! Rabbit holes…

image

image

image

7

a left join will not magically create a July, you must have an order in July. You could start with the table FiscalPer thats assuming you have 12 periods, for us it wouldn’t work. Maybe Company Calendar or some other table that has Months, otherwise you will have to build one with FOR XML.

Idea 1:

image
image700×156 6.46 KB

Idea 2:

image
image977×135 4.23 KB

Idea 3:
MonthlySales from Epicor Demo Database

image
image1212×764 27.8 KB

image
image952×335 51.2 KB

3 Likes
8

Haso,
Awesome!!! When I created a new test BAQ by adding Fiscal Calendar first, and then OrderDtl, the missing rows showed up with a left join. TY!

Yet when I try to add Fiscal Calendar 2nd, after OrderDtl, no matter what, the missing rows do not appear, even if I do a full outer join.

Do I need to rebuild the BAQ, making sure to add Fiscal Calendar before OrderDtl? Is there a shortcut way of making that change without needing to delete OrderDtl and re-creating everything associated with it?

Is that a quirk with BAQ’s?

image
image760×434 20.5 KB

image

vs (no missing lines):

image
image799×457 15.7 KB

image

9

You can reorder them in Table List (the tab before Table Relations).

To be fair, I run into a wall sometimes if I throw a subquery in there and then try to reorder. Sometimes it’s fine; sometimes I get 1 2 2 4.

Ha, that’s much better than what I have tried to do before, which is a distinct query of some table with dates. I will remember that.

1 Like
10

Ooh… don’t call a calculated field “Year” or “Month” or “day.” You’re asking for trouble.

image

You’re fine to make the description “Year,” but name it TheYear or anything else.

5 Likes
11

You just made me realize that’s why the BAQ would seemingly just randomly not like my syntax in a calculated field. I’ve made a bad habit of calling calculated fields things like Year, Month, etc. I just learned today that Row is another one that you need to not use.

2 Likes
12

It’s deceptive because (a) it allows it and (b) it works sometimes. But if you calculate off of a calculated field in the same subquery, for example, it calls it by Row rather than Calculated_Row

And probably many other reasons that are clearer to SQL people.

13

Without a doubt, that’s why it seemed sporadic. I’m sure sometimes it was referenced within it’s own subquery and other times it wasn’t.