topic Re: How to calculate Percentile based on Excel Percentile Rank function in QlikView

How to calculate Percentile based on Excel Percentile Rank function

BootCam — Sat, 16 Nov 2024 05:11:50 GMT

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,

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

Anil_Babu_Samineni — Tue, 20 Nov 2018 04:00:04 GMT

Try this way?

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

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

BootCam — Tue, 20 Nov 2018 05:05:47 GMT

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.

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

tresB — Tue, 20 Nov 2018 06:50:45 GMT

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

Anil_Babu_Samineni — Tue, 20 Nov 2018 07:33:51 GMT

Is that last row is static or is that dynamic when filter happens?

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

BootCam — Tue, 20 Nov 2018 10:57:45 GMT

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

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

BootCam — Tue, 20 Nov 2018 14:27:02 GMT

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,

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

tresB — Wed, 21 Nov 2018 05:15:18 GMT

Try :

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

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

BootCam — Wed, 28 Nov 2018 21:35:15 GMT

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,

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

vinieme12 — Thu, 29 Nov 2018 07:13:58 GMT

Hi,

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

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

BootCam — Fri, 30 Nov 2018 02:06:39 GMT

Hi Vinieme12,

Thank you so much for your solution.

Best,