How to calculate Percentile based on Excel Percentile Rank function

[BootCam]

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,

[Anil_Babu_Samineni]

Try this way?

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

[BootCam]

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.

[tresesco]

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?

[BootCam]

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]

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]

Try :

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

---

[BootCam]

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 · Accepted solution]

Hi,

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