Hello,
We are a small company dealing with customer Bonus contract. We need to be able to calculated for every line o invoice the cumulative discount that the customer will get according to his contract an contract of his parent company.
We have customers that have discount per product and/or per hierarchy of product and/or on total of their sales.
Those customers can cumulate discount with the discount of their Parent company.
Exemple :
Product and Customer Table:
Customer "4" has a parent customer "1".
The Product "A" that belongs to no Hierachy
The product "F" that belongs to Hierachy "I"
Contract:
The customer "4" has the following contract:
For the sales of 2018, Customer "4" will get 2% on all the sales of all Product
For the sales of 2018, Customer "4" will get 5% on the sales of product "A"
The customer "1" and all the child company( including customer "4") has for contract:
For the sales of 2018, Customer "1" and his child company will get2,5% on the sales of the products belonging to Hierachy "I"
Invoice line (and expected results in yellow)
| CUSTOMER_CODE | PRODUCT_CODE_Invoice | AMOUNT | YEAR_Invoice | Bonus |
| 4 | A | 50 | 2018 | 7,00% |
| 4 | B | 75 | 2018 | 2,00% |
| 4 | F | 100 | 2018 | 4,50% |
| 4 | F | 125 | 2017 | 0,00% |
I have included a small qlikview file to make it a bit more self explainatory althought my data model is not optimized: Proposal are very welcome.
