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.
