Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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?

Life is so rich, and we need to respect to the life !!!
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))

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))

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