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

    Weighted allocation of discount invoice

    Justin Dallas

      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.

       

      BadBillTos.PNG

       

      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.