topic Re: Expession Help in QlikView

Expession Help

Mon, 07 Jul 2014 14:33:34 GMT

I am looking to calculate what the commission would be for each salesperson. The more units they sell the higher percentage they make on each sale.

For example if you sell < 500,000 total for a month , you make .0035 on each sale. Over 500,000 you make .0050 on each sale.

I tried using Aggr function for the first part, but no luck.

if(Aggr(sum(loan_amt),LoanOfficer)<=500000,(loan_amt * .0035),0)

It only returns the aggr value on1 record not all.

Any help is appreciated.

Re: Expession Help

joshabbott — Mon, 07 Jul 2014 14:41:08 GMT

Hello,

I'm assuming your chart has a dimension of salesperson? If so, try something like this:

=(sum(loan_amt) * .0035) + ((sum(loan_amt) - 5000) * .0015)

Re: Expession Help

datanibbler — Mon, 07 Jul 2014 14:46:14 GMT

Hi Robert,

why so complicated?

I assume that in your base_data table, you have one line for every individual sales_process, yes?

=> So first you'd have to aggregate your table by salesperson to find out how much every one of them sold.

That is in the help_file. The important things about this are that you need a GROUP BY clause and

you have to take care to keep only those fields you want in your aggregation - just think logically:

==>> QlikView will group by every field you have in the GROUP BY clause - so if you keep the item_nr in there, you will get only very small sums - the total of sales of one specific item by one salesperson, which is probably not what you want.

Better keep just the date, salesperson and [sales] field for the aggregation and join the remainder afterwards.

=> So then you know how much any individual salesperson sold and then you can generate an additional field with the commission by using an IF_construct and the tresholds you have.

HTH

Best regards,

DataNibbler

Re: Expession Help

martinpohl — Mon, 07 Jul 2014 14:46:28 GMT

for the total, you need to sum the results for each record

sum(aggr(if(sum(loan_amt)<=500000,sum(loan_amt)*.0035,0),LoanOfficer))

Re: Expession Help

its_anandrjs — Mon, 07 Jul 2014 14:49:03 GMT

Try like

=If( Sum(TOTAL loan_amt) <= 500000, (Sum( loan_amt ) * .0035) , (Sum( loan_amt ) * .0015 ) )

Re: Expession Help

MK_QSL — Mon, 07 Jul 2014 14:55:49 GMT

IF(SUM(loan_amt)<500000, SUM(loan_amt)*.0035,

IF(SUM(loan_amt)>=500000, SUM(loan_amt)*.0050))

EDIT:

If you want the total amount of commission, use below…

SUM(

AGGR(

IF(

SUM(

loan_amt)<500000,

SUM(

loan_amt)*.0035,

IF(

SUM(

loan_amt)>=500000,

SUM(

loan_amt)*.0050)),

LoanOfficer))