Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

paulchittenden
New Contributor

Calculating Commission per period, summarised per individual

I have hopefully simplified my problem with calculating commission per period, but just showing a Total

I can show the commissions on a sale, per salesperson, per period.

PeriodSales PersonGross SalesCommission
JanuaryMary£1,00050

FebruaryMary£2,000£150
MarchMary£5,000£450

I would like a separate table that simply sales and Commission

Sales PersonGross SalesCommission
Mary£8000650

Whilst i can simply do Sum(Gross Sales) for Gross Sales.

Because the Commission is say 5% on the first £1000 per month, then 10%.  I need to out commission per period, then Sum the totals

What function should I do to get the Commission to work it out per period, then total?

Thanking you in advance.

6 Replies

Re: Calculating Commission per period, summarised per individual

Is Sum(Commission) didn't work for you?

paulchittenden
New Contributor

Re: Calculating Commission per period, summarised per individual

Thanks for the quick reply.

in my data I only have the Gross Sales, and the commission is worked out in a Table, so I don't have a lists of Commission Date to Sum.

Thus I can work out the commission per given period,

But I can not have a "summary table"

aarkay29
Valued Contributor

Re: Calculating Commission per period, summarised per individual

may be this

Sum(Aggr(Commission,Period))

MVP
MVP

Re: Calculating Commission per period, summarised per individual

May be just this

Table:

LOAD Period,

     SalesPerson,

     GrossSales,

     Commission

FROM ...;

AggrTable:

LOAD SalesPerson,

     Sum(GrossSales) as AggrGrossSales,

     Sum(Commission) as AggrCommission

Resident Table

Group By SalesPerson;

aarkay29
Valued Contributor

Re: Calculating Commission per period, summarised per individual

If i understood correctly, I believe you have Commission Percent for each period and you want to calculate the commission amount. If that is the case may be this


Sum(Aggr((Gross Sales*Commission)/100,Period))

Highlighted
paulchittenden
New Contributor

Re: Calculating Commission per period, summarised per individual

I have in my data, a list of sales, and the day they occurred, and the salesperson.

So I can use basic functions, to total sales per month per sales person.

The commission calculations is on a sliding scale.

So if I break sales down into per month and apply the sales, then I get the commission per month, which the table can total.

I would like a separate table for the total year.

Easy to work out sales for the year, by just

DIM                              Measure

Salesperson                    SUM(GROSS SALES)

But if I apply the commission formula it will work it out on the Totaled Gross sales, whereas I would like it to work out Commision per month, then totaled

if that makes sense.

Community Browser