# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
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 (2)

• ### Percentile

1 Solution

Accepted Solutions
Champion II

Hi,

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

10 Replies

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)
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 Percentile 71.4% Wand of Sell Items Peer Group 83.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

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

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

Creator
Author

Hi Tresesco,

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

Try :

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

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

Champion II

Hi,

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