Weighted allocation of discount invoice

[JustinDallas]

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:
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.

[sunny_talwar]

What exact number are you after?

[JustinDallas]

Here is what I am hoping to get Sunny,

Bill To	AllocationCompensatedSum
Oracle	183.33
QlikTech	146.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

[Vegar]

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

[JustinDallas]

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"

[sunny_talwar · Accepted solution]

Try this

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

+

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

[Vegar]

Let the expression do something like this :

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

[JustinDallas]

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.

[sunny_talwar]

Awesome, glad you were able to work it out