Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

lefzam00
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!

MonthCountrySalesFTERegion
          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,79716 Americas
          4 USA 8,266 7 Americas
          1 Canada 4,70 15 Americas
          3 France 8,45518 Europe
          1 Canada 2,877 17 Americas
          2 USA 5,90115 Americas
          5 UK 9,053 11 Europe
          1 Canada 1,704 4 Americas
          5 UK 6,22019 Europe
          2 USA 7,296 12 Americas
          2 China 9,15917 APAC
          2 Germany 1,008 9 Europe
          5 Canada 3,128 5 Americas
          3 China 1,009 8 APAC
          1 Germany 6,98713 Europe
          5 Germany 8,414 14 Europe
          4 Germany 9,010 16 Europe
4 Replies

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)

lefzam00
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

Re: Fractile & Aggr function for Benchmarks

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

MVP
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?