Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
BootCam
Contributor

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 (2)
1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: How to calculate Percentile based on Excel Percentile Rank function

Hi,

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

PercentRank.PNG

10 Replies

Re: How to calculate Percentile based on Excel Percentile Rank function

Try this way?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
BootCam
Contributor

Re: How to calculate Percentile based on Excel Percentile Rank function

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.

MVP
MVP

Re: How to calculate Percentile based on Excel Percentile Rank function

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

Re: How to calculate Percentile based on Excel Percentile Rank function

Is that last row is static or is that dynamic when filter happens?
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
BootCam
Contributor

Re: How to calculate Percentile based on Excel Percentile Rank function

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
Contributor

Re: How to calculate Percentile based on Excel Percentile Rank function

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

 

MVP
MVP

Re: How to calculate Percentile based on Excel Percentile Rank function

Try :

 

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

 




BootCam
Contributor

Re: How to calculate Percentile based on Excel Percentile Rank function

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
Esteemed Contributor II

Re: How to calculate Percentile based on Excel Percentile Rank function

Hi,

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

PercentRank.PNG