Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ben2roberts
Contributor

Count If Greater Than Percentile

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_DESCRELN_NBR 2018 CNR CM Clients 50PCT 50PCT (Using TOTAL)
16065 1085 16,065
EMEACLIENT1137801378016,065
EMEACLIENT21389138916,065
EMEACLIENT378178116,065
EMEACLIENT411611616,065

Result Table

Region# clients >50th Percentile
EMEA2
6 Replies
MVP & Luminary
MVP & Luminary

Re: Count If Greater Than Percentile

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

- Marcus

ben2roberts
Contributor

Re: Count If Greater Than Percentile

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

ben2roberts
Contributor

Re: Count If Greater Than Percentile

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.

MVP & Luminary
MVP & Luminary

Re: Count If Greater Than Percentile

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

ben2roberts
Contributor

Re: Count If Greater Than Percentile

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.

MVP & Luminary
MVP & Luminary

Re: Count If Greater Than Percentile

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