calculating percentile of any value in an array/set

[qlikuser499288]

I have an aggregation field that takes into account four different dimensions as filters. So something like this:

Sales = sum({$<fct_tbl={1},dt_lv= {'Week'}, [ROWNO()] = {"> $(vLevel1) <= $(vMzx2)"} >}D_Sales)

Let's say I have a dimension called Country with 5 values = [USA, Turkey, England, France, Germany] 

And I want to find percentile of each of these countries such that if USA has the most sales, percentile of USA would be 1 and if France has the lowest sales, France's percentile would be 0 (also, exclusive/inclusive calculations would be good to have). 

I've seen fractile but to my knowledge, fractile(sum(sales),fraction) requires a fraction qualifier --plus doesn't work with nested aggregations. How can I achieve this?

Thanks! 

[Brett_Bleess]

Seems to me you would need to AGGR by country in this case?  Here is a Design blog post on AGGR and Set Analysis, hopefully that may help.

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

[rubenmarin]

Hi @qlikuser499288, to add to @Brett_Bleess answer, ti can be something like:

sum({$<fct_tbl={1},dt_lv= {'Week'}, [ROWNO()] = {"> $(vLevel1) <= $(vMzx2)"} >}D_Sales) / Max(TOTAL Aggr(sum({$<fct_tbl={1},dt_lv= {'Week'}, [ROWNO()] = {"> $(vLevel1) <= $(vMzx2)"} >}D_Sales), CountryField))

You can try first the bold part of the expresion to check if it really returns the max country value.