Baq data is not accurate

I’m new to making BAQs, I can get part of it to work but not all, and just when I think I have it all, I check in depth and its wrong.

The BAQ is meant to show data for a specific customer from 01/01/2021 to present day of;

Part, Part Description(Customers Part name),Total units sold, Price per unit, Total cost per unit, Lbr Cost, Mtl Cost, Bur Cost, Sub Cost, And Profitability%

I’ve managed to get the part, description, units sold to come up accurately

Price per unit, I settled on a Calculated field for Max(unit price)

But the costs do not work, or populate, or they’re incorrect in comparison to what is shown in part advisor.

For the phrase build, I’ve used; InvcDtl, InvHead, Customer and Part
I’ve set table criteria for Customer Name Constant
Also table criteria for Invoicedate > 1/1/2021
I also had them grouped by part and part desc

Am I not doing this right?

I’m extremely new to this and still trying to learn as nobody here seems to know about creating BAQs so I’ve been going in blindly basically learning through trial and error

Can you post screencaps of your tables in the Designer, and the script for your calculated field?

This is when I thought I got the Total unit cost working right, but was mostly wrong in comparison to part advisor

Bumping this post in hopes someone can help

Part Advisor uses PartCost table that in fact uses your costing method (average,STD etc) According to your current BAQ you are using InvDtl, so you are comparing different numbers. InvDtl will outline just that specific event (invoice) and not the Unit Cost for the part under your inventory value.

1 Like

Thank you for the clarification, I should be able to make some headway now.