Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

 

@sunny_talwar 

 

 

 

 

1 Reply
Highlighted
Sergey_Shuklin
Valued Contributor

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

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:

perc_calc_pic1.png

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

Tags (2)