Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nicolasmottebau
New Contributor

How to Sum value associted with Hierachy (Bonus sales agreement)

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_CODEPRODUCT_CODE_InvoiceAMOUNTYEAR_InvoiceBonus
4A5020187,00%
4B7520182,00%
4F10020184,50%
4F12520170,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.

1 Reply
Highlighted
nicolasmottebau
New Contributor

Re: How to Sum value associted with Hierachy (Bonus sales agreement)

with the file