Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: **REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Count If Greater Than Percentile

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

ben2roberts

Creator

2018-09-05
05:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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_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 |

1,188 Views

6 Replies

marcus_sommer

MVP & Luminary

2018-09-05
07:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Marcus

1,099 Views

ben2roberts

Creator

2018-09-05
10:41 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,099 Views

ben2roberts

Creator

2018-09-05
11:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,099 Views

marcus_sommer

MVP & Luminary

2018-09-06
03:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,099 Views

ben2roberts

Creator

2018-09-06
03:54 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,099 Views

marcus_sommer

MVP & Luminary

2018-09-06
04:16 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,099 Views