# Qlik Sense App Development

New Contributor II

## Fractile & Aggr function for Benchmarks

Hi,

Using the table below, I would like to compare performance (Sales / FTE) of each country to its respective region's 25% Quartile performance. I need to calculate the following 2 KPIs:

1) Country Sales/ FTE - I am guessing: aggr(sum(Sales)/sum(FTE),Country)

2) Regions Sales/ FTE 25% Quartile - which needs to remain at Region level when one country is selected

Any tips would be much appreciated! Many thanks in advance!

 Month Country Sales FTE Region 5 USA 4,390 13 Americas 4 France 2,013 15 Europe 3 Germany 1,385 7 Europe 5 China 5,997 5 APAC 2 Germany 4,154 7 Europe 5 Canada 5,797 16 Americas 4 USA 8,266 7 Americas 1 Canada 4,70 15 Americas 3 France 8,455 18 Europe 1 Canada 2,877 17 Americas 2 USA 5,901 15 Americas 5 UK 9,053 11 Europe 1 Canada 1,704 4 Americas 5 UK 6,220 19 Europe 2 USA 7,296 12 Americas 2 China 9,159 17 APAC 2 Germany 1,008 9 Europe 5 Canada 3,128 5 Americas 3 China 1,009 8 APAC 1 Germany 6,987 13 Europe 5 Germany 8,414 14 Europe 4 Germany 9,010 16 Europe
4 Replies
MVP

## Re: Fractile & Aggr function for Benchmarks

If you are talking about KPIs then use

1) Sum(Aggr(sum(Sales)/sum(FTE),Country))

2) Sum(TOTAL <Region> Sales)/Sum({<Country = {"=Sum(FTE) < 0.25"}>}FTE)

New Contributor II

## Re: Fractile & Aggr function for Benchmarks

Unfortunately, that doesn't work The first formula gives me a very high number and the second formula returns an error

MVP

## Re: Fractile & Aggr function for Benchmarks

Let's talk about first formula first, What number you are expecting?

MVP

## Re: Fractile & Aggr function for Benchmarks

 1) Country Sales/ FTE - I am guessing: aggr(sum(Sales)/sum(FTE),Country) 2) Regions Sales/ FTE 25% Quartile - which needs to remain at Region level when one country is selected

Do you envision them in a chart or a KPI object? I would think chart because you seem to want the 1st one per country, right? 2nd one seems to be per region, so region and country (maybe?) as dimension?