7 Replies Latest reply: Oct 23, 2018 10:03 AM by Sunny Talwar

# Weighted allocation of discount invoice

Hello 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:
;
[
'OrderNumber', 'BillTo'
1, 'QlikTech'
2, 'QlikTech'
3, 'Oracle'
]
;

InvoiceOrderRels:
[
%orderNumber, %invoiceNumber
1, 1A
2, 2A
3, 3A
1, SUMMARYDISCOUNTINVOICE
2, SUMMARYDISCOUNTINVOICE
3, SUMMARYDISCOUNTINVOICE
]
;

Invoices:
;
[
'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.

• ###### Re: Weighted allocation of discount invoice

What exact number are you after?

• ###### Re: Weighted allocation of discount invoice

Here is what I am hoping to get Sunny,

 Bill To
AllocationCompensatedSum
Oracle183.33
QlikTech146.66

The calculation logic would look seomthing like this

Sum(Oracle) - (Sum(Oracle)/Sum(Oracle + QlikTech) * Sum(Discount Invoice)) => 183.33

250 + ( (250/(250 + 200)) * -120 ) => 183.33

• ###### Re: Weighted allocation of discount invoice

Try this

Sum({<InvoiceType = {'OrderInvoice'}>}BillAmount)

+

((Sum({<InvoiceType = {'OrderInvoice'}>}BillAmount)/Sum(TOTAL {<InvoiceType = {'OrderInvoice'}>}BillAmount)) * Sum({<InvoiceType = {'SummaryDiscountInvoice'}>}BillAmount))

• ###### Re: Weighted allocation of discount invoice

Hello Sunny,

This wasn't quite the solution, but it started me down the right path which is what I was looking for.  My final expression statement as far as I can tell is this:

```(
SUM(If(IsNull([Is Summary Invoice]), [Total Revenue]))
+
(
SUM( TOTAL <[Master Bill Number]> {<[Is Summary Invoice]={'1'}>}[Total Revenue])
*
(
SUM(If(IsNull([Is Summary Invoice]), [Total Revenue])) /
SUM( TOTAL <[Master Bill Number]> If(IsNULL([Is Summary Invoice]),[Total Revenue]))
)
)
) / SUM({<[Charge Type]={'DEL'}>}Count)
```

The key point that was I was missing was the SUM( TOTAL <Master Bill Number>... line. The Master Bill Number is how the SummaryInvoices, and ChildInvoices are related, so I have to group by that.

• ###### Re: Weighted allocation of discount invoice

Awesome, glad you were able to work it out

• ###### Re: Weighted allocation of discount invoice

You are doing sum of transactions and only have one hit for your discount in the transaction table when choosing Qliktech.

• ###### Re: Weighted allocation of discount invoice

Yes, but I don't want that one hit of the discount for each "invoice by billto", I would rather have the hit be proportional to the sum of the "invoices by billto"

• ###### Re: Weighted allocation of discount invoice

Let the expression do something like this :

sum( aggr( Sum(BillAmount ), OrderNumber)),