Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wim_rijken
Partner - Contributor II
Partner - Contributor II

Get record value based on expression calculation

I have a chart in where I calculate the SalesAmount per Salesperson.

This is a calculation based on a lot of different sales figures, filtered with set analysis to get the correct result.

It is not doable via scripting, so in my script, I do not have the correct SalesAmount per Salesperson

this is an example of the result of the calculation in the chart, but be aware, this is a calculation of a lot of different sales amounts:

SalespersonSalesAmount
Dave5000
Jef10000
Michael15000

Now I have a table (Excel spreadsheet) where I upload a "SalesAmount" and "Bonuspercentage".

(so salesamount between 0-2500 = 5%, between 2501-12500 = 10%, between 12501-20000 =15%)

SalesAmountBonusPercentage
25005
1250010
2000015

I would like to link them together, so as a final result I would like to see:

SalespersonSalesAmountBonusPercentage
Dave500010
Jef1000010
Michael1500015

Is there a way to get the correct "BonusPercentage" based on the calculated field "SalesAmount" in the chart?

2 Replies
Or
MVP
MVP

I got a little lost trying to do exactly what you said, so I tried a slight workaround. Instead of using a table with SalesAmount and BonusPercentage, I used a table with BonusStart, BonusEnd, and Bonus Percentage. Hopefully you can make that adjustment in your Excel, but if not, you can use Peek() to do it in your script. There may be a way to get this done with your original data structure but I'm not sure how.

Load * Inline [

Salesperson,    SalesAmount

Dave,    5000

Jef,    10000

Michael,    15000

Wile E. Coyote, 1000];

Load * Inline [

BonusStart, BonusEnd, BonusPercentage

0, 2500,    5

2501, 12500,    10

12501, 20000,    15

];

Note that this is a data island - it does not connect to SalesAmount (which you don't want it to). I then used the following expression:

Sum(aggr(if(Sum(SalesAmount)>=BonusStart AND Sum(SalesAmount) <= BonusEnd,BonusPercentage),BonusStart,Salesperson))

Note that this will also work if the bonus table is connected to the sales table via dimensions such as Salesperson:

Load * Inline [

Salesperson, BonusStart, BonusEnd, BonusPercentage

Dave, 0, 2500,    5

Dave, 2501, 12500,    10

Dave, 12501, 20000,    15

Jef, 0, 2500,    11

Jef, 2501, 12500,    22

Jef, 12501, 20000,    33

Michael, 0, 6666,    6

Michael, 6667, 66666,    66

Michael, 66667, 666666,    666

];

balabhaskarqlik

May be like this:

If(Column(1) >= 0 and Column(1) <= 2500, "5",

if(Column(1) > 2500 and Column(1) <= 12500, "10",

If(Column(1) > 12500 and Column(1) <= 20000, "15")))

Column(1) refers SalesAmount.