Stuck - Pivot Aggregate Formula

Can you do a case statement in the pivots’ aggregate? I can’t seem to get it to work, lol.
The issue is that when a count is returned null in the pivot nothing is returned but I want the remaining data returned. This only happens in the pivot. Any advice to get the data I need is appreciated.

sum( case when Calculated_PurchaseCount = null then 0 else Calculated_PurchaseCount end )

Could you try
case when Calculated_PurchaseCount != null then Calculated_PurchaseCount
else 0

1 Like

That’s not working either. I tried to eliminate the null is the sub query as well and that’s not working.

Change you calculated field in outermost value, if it is complex…eg:


I tried this and are getting the same results. :worried:

Purchase_Count sounds like an aggregate calc value in another subquery, I think you need to eliminate the nulls the first time the field is referenced.
If the first reference is an aggregate then you can do something like this:

If that doesn’t work, can you post your query, context is everything.

1 Like

See attachment.

You need to put your IsNull in each of your Calculated field months,like this:

Your Pivot Aggregate does not need the IsNull function.
(I made a mistake on my earlier post the IsNull is best inside the Aggregate function to avoid other issues)

Here is the corrected file:
RLB-InventoryAnalysis.baq (29.6 KB)


Thanks Rick. I’m still only getting 7062 returns. I should get over 39K. I was able to replace NULLS with zeros before but for all parts that have NULLS across the Months aren’t returning.

FYI - I am traveling today, but until later: I suspect that you are expecting records and data for things that don’t have any records? I would have to look at that BAQ again but you might have an inner join where you might want a left join so that ALL parts are returned instead of just parts with matching records in the other table.

1 Like

I have all left joins up to the part numbers so I would have assumed it would have returned part numbers with no data. Hope to talk later when your free. I am going to move on to another BAQ for now :slight_smile:

I figured out what wrong. Thanks everyone for the response.

I had to add the part table to the Top level query and do a left join to the sub-query to get all the part number to return. Silly me :slight_smile:

1 Like

Glad you figured it out!
I was just going to ask about the criteria on the Part table, thinking that was limiting your results.
Nice job!

1 Like