Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Incentives - Data modeling issue

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:

TiersPayout
[1;24]50£
[25;49]75£
[50;74]100
>74125

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:

SalesRepCountryYearQ1Q2Q3Q4
TotoUK201718252730
TitiUK201726282425
TataUk201728222428

The Kicker for the UK:

TiersVolumeKicker
1>6020%
2[50;60]25%
3<=3070%


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.