Incentives - Data modeling issue

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I need some help please for data modeling.

I would like to build a dashboard to help the sales team to keep track of their individual earnings on the 2017 Incentive scheme

I have bonus payment matrix, per country, which is defined by “band”. The bands are different for each country

For example, for the UK:

Progress through the band levels is based on cumulative sales volume for the individual through the year

If the individual achieves their quota for a given quarter, they receive a Kicker

The kicker is a percentage of earnings in that quarter.
Each person has a different Kicker, but the kicker % will remain the same in each quarter

The targets for the UK for example:

SalesRep	Country	Year	Q1	Q2	Q3	Q4
Toto	UK	2017	18	25	27	30
Titi	UK	2017	26	28	24	25
Tata	Uk	2017	28	22	24	28

The Kicker for the UK:

I have my fact table where i have the volume (#SalesQty) where i have the SalesRep, the country and the salesdate.

I would like to :

Show progress towards annual target (e.g. a progress bar or thermometer) with the ability to distinguish between different quarters
Show current quarter sales and target (to make it clear if you will reach your quota for the current Quarter and earn a kicker), and the cumulative sales YTD (affects which band you are earning in)
Show a breakdown of the users earnings to date

Could you please help me to build my data model, how to link the volume to the bands,and how to calculate the metrics?

Many thanks by advance.

topic Incentives - Data modeling issue in QlikView