2 Replies Latest reply: Mar 27, 2013 4:07 AM by Robert Svebeck RSS

    Advanced Customer Bonus Calculation

    Robert Svebeck

      Hi

       

      I need some guideance on how to approach this problem:

       

      We have 650 customer contracts. The contracts are stored in a table like this:

       

      CustomerContracts:

      Contact IDCustomer numbersValid for sales on Item groupsValid to dateOrdertypeBonus
      10201, 2,310,40,602013-10-31110%
      10306,4,9202013-12-311,2,34%
      10501, 2,310,202013-12-311,22%
      etc

       

      As you can see, some of the contracts are overlapping - one customer number exists on several contract ID's with small differences in the contract details.

       

      The bonus is supposed to be calculated based on actual sales and is supposed to be presented each month. The sales transactions looks like this:


      SalesTransactions:

      Transaction DateOrdertypeCustomer numberItem groupAmount
      2013-03-181110200
      2013-03-192110300

      One single transaction row can generate bonus on several different contracts.

       

       

      What method can be used to calculate the bonus to be paid to for each Contract ID?

       

      My problem is the many-to-many dilemma. One contracts is for many transcation rows, and one transaction row is for many contracts.

       

      Appreciate any suggestions.

        • Re: Advanced Customer Bonus Calculation

          Hi,

           

          I believe the best would be to model the table 'CustomerContracts', making a line for each 'Contact ID' and 'Customer Number' as a key. So you can relate correctly.

           

          By Rebeca

            • Re: Advanced Customer Bonus Calculation
              Robert Svebeck

              Thank you Rebeca for your advice.

               

              I have tried this method. I created a new table in this kind of key.

               

              But I still have an issue and that is how do I summarize transactions for each "contract_id_customer_number_key"?

               

              Remember that one single transaction row can refere to several "contract_id_customer_number_key".

               

              I have many to many relations - one transaction row can be refered to many contracts, and one contract can be refered to many transactions.

               

              If I try to do this with set analysis it does not work, since the dimensions in a chart can not be refered to in a set analysis. And building extremely nested "if...then", will just take the breath out of my server and my mind...

               

              My current approach is to solve this in my script completely. Making a for each loop on my contracts and reading my entire statistics file for each contract. This takes time, since I have 650 contracts and around 9 million transaction rows for each contract. It currently takes 4 hours to do this, it works, but I doubt this is the bet solution.

               

              I was hoping there was a solution with intervall match or something else so that I do not have to read all my statistics over and over again.

               

              Robert