topic Re: Calculating Commission per period, summarised per individual in QlikView

Calculating Commission per period, summarised per individual

paulchittenden — Thu, 08 Jun 2017 14:47:13 GMT

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.

Period	Sales Person	Gross Sales	Commission
January	Mary	£1,000	50
February	Mary	£2,000	£150
March	Mary	£5,000	£450

I would like a separate table that simply sales and Commission

Sales Person	Gross Sales	Commission
Mary	£8000	650

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.

Re: Calculating Commission per period, summarised per individual

Anil_Babu_Samineni — Thu, 08 Jun 2017 15:16:01 GMT

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

Re: Calculating Commission per period, summarised per individual

paulchittenden — Thu, 08 Jun 2017 15:18:39 GMT

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"

Re: Calculating Commission per period, summarised per individual

aarkay29 — Thu, 08 Jun 2017 15:19:37 GMT

may be this

Sum(Aggr(Commission,Period))

Re: Calculating Commission per period, summarised per individual

sunny_talwar — Thu, 08 Jun 2017 15:23:51 GMT

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;

Re: Calculating Commission per period, summarised per individual

aarkay29 — Thu, 08 Jun 2017 15:37:55 GMT

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

Re: Calculating Commission per period, summarised per individual

paulchittenden — Thu, 08 Jun 2017 16:10:34 GMT

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.