How to Calculate PERCENTILE.EXC OF Excel in Qlikview

[rayeesav]

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

@sunny_talwar 

[Sergey_Shuklin]

Hello!

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.