Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

New Contributor

How to Calculate PERCENTILE.EXC OF Excel in Qlikview

Hi Everyone,

I am trying to calculate percentile in Qlikview, but getting blank rows with below formula of percentile.

Aggr((Count(TOTAL{<State>} DISTINCT State)-Rank(Sum({<State>}SalesAmount)*SUM({<State>}Qty)))/Count(TOTAL {<State>} DISTINCT State),State)

I have attached the sample excel file with data tab and result tab.

I want a straight table with 4 dimensions Product, ProductCategory, State, SoldNotSld and I want 2 expressions as below, The Formula given below is from excel need to convert them into Qlikview expressions.

1.NetSales1000 = SalesAmount*Qty

2.Percentile =PERCENTILE.EXC(IF($C$2:$C$13=C2,$G$2:$G$13),G2)

Please let me know how do we achieve the result tab in Qlikview. 


Appreciate your help!!







1 Reply
Valued Contributor

Re: How to Calculate PERCENTILE.EXC OF Excel in Qlikview


Logic for excluded percentile we have to calculate with own hands (because Fractile() finds included percentile).

You'll find comments of logic inside expression:


There are several repeated conditions you may lock them into a variable or support column.

Tags (2)