Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rsvebeck
Valued Contributor

Advanced Customer Bonus Calculation

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.

2 Replies
Not applicable

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

rsvebeck
Valued Contributor

Re: Advanced Customer Bonus Calculation

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

Community Browser