4 Replies Latest reply: Aug 14, 2017 7:26 AM by Sunny Talwar RSS

    Fractile & Aggr function for Benchmarks

    Lefteris Zampoulakis

      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