Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
BootCam
Creator
Creator

How to calculate Percentile based on Excel Percentile Rank function

Hi,

 

Can Anyone help me with percentile calculation based on the attach file? I just dont know how to use the PercentileRank.EXC function in qlikview to reach the desired percentage numbers in Qlikview.

 

The calculations are made in Excel but need a bit help to translate that into Qlikview.

 

Thanks,

 

Labels (1)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Hi,

Attached app demonstrates working of both PercentRank and PercentRank.EXC functions

PercentRank.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

10 Replies
Anil_Babu_Samineni

Try this way?

Fractile(Sum(Wand), 0.9) - For 90th Percentile

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
BootCam
Creator
Creator
Author

Hi Loveisfail,

 Thanks for your reply. I am trying to get what is the percentile for a particular Wan Item than getting the 90th or other percentile. Is there any way i can achieve the numbers like this as mentioned in the excel file:

Wand Percentile71.4%
Wand of Sell Items Peer Group83.3%

 

At the end of the attached excel file has this calculations but with excel's own function. I would like to get the same numbers using Qlikview functions. Fractile function is not helping here. 

Is there any other way to do it?

Thanks.

tresesco
MVP
MVP

I have written the expression for - 'Wand Percentile' in the attached sample and leaving it to you for the other part. Hope this helps.

 

If(ValueList('Wand Percentile', 'Wand of Sell Items Peer Group')
='Wand Percentile',
Count({<Wand={"<=$(=Only({<Item={'XYZ'}>} Wand))"}>}Item)
/
(
Count({<Wand={"<=$(=Only({<Item={'XYZ'}>} Wand))"}>}Item)
+
Count({<Wand={">=$(=Only({<Item={'XYZ'}>} Wand))"}>}Item)
)

Anil_Babu_Samineni

Is that last row is static or is that dynamic when filter happens?
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
BootCam
Creator
Creator
Author

Hi Loveisfail, 

 

Last row should be dynamic. Instead of 'XYZ' it could be any value based on selections. But the peer items are constant.

 

Thanks,

Awal

BootCam
Creator
Creator
Author

Hi Tresesco,

Thanks for your reply.

What if the Last row is dynamic? Meaning that if i select any item from the list box, then how the calculation likely to change by selections?

Regards,

SA

 

tresesco
MVP
MVP

Try :

 

=Count({<Item,Wand={"<=$(=only(Wand))"}>}Item)
/
(
Count({<Item,Wand={"<=$(=only(Wand))"}>}Item)
+
Count({<Item,Wand={">=$(=only(Wand))"}>}Item)
)

 




BootCam
Creator
Creator
Author

Hi, Tresesco,

 

The formula doesnt give me the correct value for XYZ. The number 71.4% shows as a total percentage when i select XYZ.

Still dont understand how the Excel PercentileRank formula works in qlikview.

Any help is appreciated.

 

Thanks,

BC

 

vinieme12
Champion III
Champion III

Hi,

Attached app demonstrates working of both PercentRank and PercentRank.EXC functions

PercentRank.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.