4 Replies Latest reply: Feb 25, 2013 8:38 PM by todonnell

# PERCENTRANK - No one seems to answer

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.

• ###### Re: PERCENTRANK - No one seems to answer

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

Juan Gerardo

• ###### Re: PERCENTRANK - No one seems to answer

Wrong ut i finally was able to build it out

• ###### Re: PERCENTRANK - No one seems to answer

Hi Todonnell,

Can you provide the solution to your question?

Bill

• ###### Re: PERCENTRANK - No one seems to answer

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