Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
nicolasmottebau
New Contributor

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

with the file