PERCENTRANK - No one seems to answer

[#1]

Hello.

Does anybody know which is the equivalent QlikView function of PERCENTRANK from Excel?

PERCENTRANK(array,x,significance) returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set.

Thank you very much.

[#2]

In a chart, you can use Rank() / Count(), this will give the % rank for the value in QlikView.

Juan Gerardo

[#3]

Wrong ut i finally was able to build it out

[Bill_Britt]

Hi Todonnell,

Can you provide the solution to your question?

Bill

[#5]

Definition Of Excel Equation is

Vk < X <= (Vk+1)

( (Target% -Vk)/ (Vk+1 - Vk)) + (k-1)) / (N-1)

X = $(vTargetPercent) = Any Number you are trying to PercentRank  over the set

N = Number of Values in Set

=' Based  on Target Percent ' &

(

(($(vTargetPercent) - FirstSortedValue({<Value={'<$(vTargetPercent)'}>}Value,-Value))//(Target% - Vk)

/

(FirstSortedValue({<Value={'>=$(vTargetPercent)'}>}Value,Value)- FirstSortedValue({<Value={'<$(vTargetPercent)'}>}Value,-Value))) //(Vk+1 - Vk)

+ (Max(aggr(Rank( aggr(sum({<Value={'<$(vTargetPercent)'}>} -1 *  Value),Name),0),Name)) -1) // Position Of Rank-1  (k - 1)

)

/(Count(DISTINCT Name) - 1) // N -1