Advanced Reporting revenue report Expression
I am building a QB Advanced Reporting report in QB Enterprise in which I want to calculate some transaction-related amounts for tax-reporting purposes. My firm is a manufacturer and distributor that sells many inventory items.
I need the report to sum the amounts on all invoice lines during a period that meet two conditions, where each invoice line is a quantity of a product sold multiplied by its unit price in that transaction. The two conditions are custom field values. So, Custom Field 1 value might be either “Y” or “N” and Custom Field 2 value might also be “Y” or “N” and where those custom field values are individually set for each product we sell. The unit price will vary by product and by invoice.
In plain English, the calculation might be, for example:
Sum all invoice lines (i.e. units sold * unit price for that item in that transaction) invoiced during a specified time period where the Custom Field 1 value for the product in that invoice line = Y and where the Custom Field 2 value for the product in that invoice line = N. (For purposes of this example, the custom field values are chosen arbitrarily.)
Can you tell me the field names (other than the custom field names) to use and the syntax to use to do the necessary calculation? I think the Expression should look something like this:
=sum ({< [Transactions.Txn Type] = {‘Invoice’} or [Transactions.Txn Type] = {‘Credit Memo’}, custom field 1 = {‘Y’}, custom field 2 ={‘N’}>} ($vExprSales{ [Transactions.Quantity with Sign] * Invoiced Unit Price})
Not sure.
As a note, a QB report in >Reports > Sales named “Sales by Items Detail” seems to use the appropriate fields for units invoiced and transaction unit price. I have done some searching and have been unable to find the field names.
Many thanks for any help you can provide.
