Weighted allocation of discount invoice
Justin Dallas Oct 22, 2018 12:25 PMHello Folks,
I've got an issue that I think is routinely solved, but I'm not sure. Long story short, if I have an Invoice that maps to 10 orders and is a discount invoice i.e the BillAmount is negative, then how do I allocate that so I don't have negative Invoice Sums.
Here is a script that demonstrates what I'm talking about.
Orders: LOAD *,OrderNumber AS '%orderNumber' ; LOAD * Inline [ 'OrderNumber', 'BillTo' 1, 'QlikTech' 2, 'QlikTech' 3, 'Oracle' ] ; InvoiceOrderRels: LOAD * Inline [ %orderNumber, %invoiceNumber 1, 1A 2, 2A 3, 3A 1, SUMMARYDISCOUNTINVOICE 2, SUMMARYDISCOUNTINVOICE 3, SUMMARYDISCOUNTINVOICE ] ; Invoices: LOAD *,InvoiceNumber AS '%invoiceNumber' ; LOAD * Inline [ 'InvoiceNumber', 'InvoiceType', 'BillAmount' 1A, 'OrderInvoice', 100 2A, 'OrderInvoice', 100 3A, 'OrderInvoice', 250 SUMMARYDISCOUNTINVOICE, 'SummaryDiscountInvoice', 120 ] ;
So if you run this script, and then look at the table, you will see that the Sums by BillTo don't make sense because the 120 is applied to both BillTos.
I can't wrap my head around what I think the set analysis should be, but I think the statement should read something like this
 Sum all the bill amounts, excluding the SummaryDiscountInvoice => SUM({<[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])
 Sum the discount provided by the summary invoice =>
SUM({<[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])
 Divide this number by the total BillAmount of Orders Ignoring our BillTo restrictions and excluding the summary =>
SUM({<BillTo=,[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])
 Multiply that by the dollar amount our BillTo is responsible for
(SUM({<[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])/SUM({<BillTo=,[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])) * SUM({<[InvoiceType]={'SummaryDiscountInvoice'}>} [BillAmount])
And this is kind of where I get stuck. Sorry for the scatterbrainededness, I'm under the weather right now.
Any help is greatly appreciated.

HowToAllocateDiscount.qvf 192.0 K