topic Re: Best way to handle Split Sales between Reps, 2 different databases ? in QlikView

Best way to handle Split Sales between Reps, 2 different databases ?

Peter_Brunner — Tue, 03 Dec 2013 06:52:02 GMT

Hi All,

I have an issue on best way to handle the following :

We Have

1) A Customer Database with Sales Reps Numbers allocated to their Customer Account Areas(Groups) that they look after.

e.g.:

Customer Table

Customer No, Customer Name, Rep Rep No

00011, Bill Const, Harry 0014

00022, Concrete, Harry 0014

00033, Timber, Harry 0014

00044, Joe Plumbing, Paul 0012

00055 Steve Posts Paul 0012

So the "Sales" these Customers Groups Generate are totaled and allocated to that Rep.

Sales Table

2) Sales Table with Customer Numbers and Different Salesman Numbers (populated by Sales Person that made the Actual Sale)

Issue how to best handle below ?

e.g: if Rep Harry looks after Customer = 00011, Bill Const ...but Paul actually made the Sale, Paul should get Sale NOT Harry!

So Paul made a $4,000 sale to 00011, Bill Const Paul = $4,000 Sales for the Day

thanks for any help on this

regards Peter

Re: Best way to handle Split Sales between Reps, 2 different databases ?

Tue, 03 Dec 2013 08:40:13 GMT

This problem is typical in Sales. I think you should recreate your model schema. For example it could be:

- Customer table with Customer no and Customer name (join Fact on Cust No)

- Sales person table with Rep no and Rep name (join Fact on Rep No)

- Fact table with actual sale fact and Customer no and Rep no (because rep which made a sale can change in time)

You can also keep Rep name+RepID in customer table for information about Rep responsible for customer. But change the name of this column and don't use it as a link between Customer and Fact table - in other words you can use it as a attribute of Customer when it's needed.

Re: Best way to handle Split Sales between Reps, 2 different databases ?

Peter_Brunner — Wed, 04 Dec 2013 01:28:01 GMT

Hi Mitosz,

thanks for your reply I will re visit this

regards Peter