Count If Greater Than Percentile

[Anonymous]

Dear all,

I am trying to create a calculation which sums the number of clients who have revenue greater than nth percentile of the clients within their region.

The table below is a pivot of a larger set of data with multiple rows per client. The sum of the revenue is calculated using the formula:

SUM({$<ASPECT = {'CNR'},FACT_YEAR = {'2018'}, Prod_Line *= {'Capital Markets'} >}AMOUNT)

I was able to calculate the 50th percentile figure in the 50PCT column

FRACTILE(

        AGGR(

          SUM({$<ASPECT = {'CNR'},FACT_YEAR = {'2018'}, Prod_Line *= {'Capital Markets'},AMOUNT-={'0'} >}AMOUNT)

       ,RELN_NBR)

    ,0.5)

But this only works when aggregated at the sum level in the table below. As soon as I try to use it in the 'results' table (counting the number of clients where revenue > 50th percentile, the result will not calculate.

I have been trying using TOTAL and NODISTINCT as guided elsewhere in some researching but I just cant seem to get it to calculate the 50th percentile at the right level to be able to apply to each client individually.

I assume the aggr approach is the right way as I essentially need a temporary table for each row which contains the results of all the rows of the table into that cell to return the 50th percentile and then run a count if > fractile result to get the final table.

Thanks,

Ben

Client Revenue Table

RGN_DESC	RELN_NBR	2018 CNR CM Clients	50PCT	50PCT (Using TOTAL)
		16065	1085	16,065
EMEA	CLIENT1	13780	13780	16,065
EMEA	CLIENT2	1389	1389	16,065
EMEA	CLIENT3	781	781	16,065
EMEA	CLIENT4	116	116	16,065

Result Table

Region	# clients >50th Percentile
EMEA	2

[marcus_sommer]

Maybe this: statistical aggregations - parts between frac 0.1 and 0.9 gives you some ideas to find a solution.

- Marcus

[Anonymous]

Thanks for the direction - not helping so far though. Could just be my understanding!

[Anonymous]

Some further thought on this:

I can calculate the correct Percentile value I want to use with the fractile formula, but it is the counting of number of relationships part that causes it to fail (I think).

The formula below gives me the figure I need at a region level.

FRACTILE(

     AGGR(

          SUM(TOTAL <RELN_NBR> {$<ASPECT = {'CNR'},FACT_YEAR = {'2018'}, Prod_Line *= {'Capital Markets'},AMOUNT-={'0'} >}AMOUNT)

     ,RGN_DESC,RELN_NBR)

     ,0.5)

When I try to apply this by counting the relationships I have where this condition is true - this is where things get a little screwy and fails.

COUNT(

     aggr(

          IF(

               AGGR(SUM({$<ASPECT = {'CNR'},FACT_YEAR = {'2018'}, Prod_Line *= {'Capital Markets'},AMOUNT-={'0'}                >}AMOUNT),RELN_NBR)

          >

               FRACTILE(AGGR(SUM(TOTAL <RELN_NBR> {$<ASPECT = {'CNR'},FACT_YEAR = {'2018'}, Prod_Line *={'Capital Markets'},AMOUNT-={'0'} >}AMOUNT),RGN_DESC,RELN_NBR),0.5)

,RGN_DESC)

,RGN_DESC)

)

In this count formula I took the approach of using the AGGR on the IF(SUM x) first part of the formula, thinking this is calculation a list of relationships and the AMOUNT.

When adding back in the fractile formula (which works independently), it does not give me the result (just 0). I confirmed the approach works by putting a number in place of the fractile part - and the count is correct.

[marcus_sommer]

I'm not sure that your aggr() and/or the nestings are defined properly. If I reduce the expression to the essentials by removing the condition/total parts it looked:

COUNT(

     aggr(IF(AGGR(SUM(AMOUNT),RELN_NBR)>

          FRACTILE(AGGR(SUM(AMOUNT),RGN_DESC,RELN_NBR),0.5)

,RGN_DESC) ,RGN_DESC))

which provides a better readability. I noticed that the various aggr contain different dimensions. This might be intended but I suggest that you check it again.

- Marcus

[Anonymous]

Thanks Marcus - I will take another look on that front.

I wondered also if creating a variable for the Fractile might be an approach so that it has some element of independence versus the count I am trying to do on specific rels.

[marcus_sommer]

Yes, if your fractile is always a total-value against your chart (independent of the used dimensions there) then you could outsource it into a variable, maybe like:

var:

= FRACTILE(AGGR(SUM(AMOUNT),RGN_DESC,RELN_NBR),0.5)

and your expression might be even simplified in this direction:

sum(-(AGGR(SUM(AMOUNT),RELN_NBR) > $(var)))

- Marcus