Payment Discounts Repeating | CLEARIFY

Hello - 

I am trying to create a report using QQube to show all expenses and income.  There are some transactions that we have bills to be paid to the same vendor, and on a monthly basis we will pay multiple of these bills with one check.  Some of these bills will have a discount for paying early.

What I am seeing is that in my report from QQube where I am showing the Account Name, Type, $ amount among other things, that these discounts are showing up repeated for the same number of bills paid in the one transaction.  So if one check paid for 10 bills, and 1 was discounted, that discounted would show up 10 times in my report.

What can I do to make it so that it will only show the discount once instead of repeating multiple times?

I don't know what particular subject you are working with, but I can generally answer this question.

As you will note in the detail subjects, e.g. sales, purchasing, job cost, inventory, the lowest granularity is at the line level.  

You will see two sets of fields:  one that refers to something that is true for the whole document e.g. document reference number, and document total, or document discount, etc. and one that refers to the specific line e.g. line amount, line qty, etc.

So one of the problems, is that a value that is for the whole document - such as discount taken -  gets repeated for each line that exists on the transaction.  Thus your issue.

If you are using something like Crystal Reports, Access, PowerBI, PowerPivot, you can use the 'DISTINCT' property so that the total document value only shows once.  Unfortunately in regular Excel, there is not an immediate way to handle this in a pivot table as 'DISTINCT' is not available in a pivot table calculated field.  

This is an admitted shortcoming of the tool, and one we would like to 'change' for regular pivot tables in a future version.

Thank you so much for the fast response!  That makes sense, and I am using Access for now to try to figure it out.  Because I am pulling all transactions, would you happen to know what field I should do the DISTINCT property on so that it doesn't repeat the discounts but keeps everything else the way it should?  The fields I am using in my table so far are as below.  Do I need to add a field and put DISTINCT on it?  There are so many fields it is hard to know which one to use!  

Thank you!

The transaction number is unique in QuickBooks across all transactions, and would be your choice. More on that here: https://clearify.com/wiki/view/206/ref-vs-transaction

Thanks that did it!