How to Calculate PERCENTILE.EXC OF Excel in Qlikview

rayeesav — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Sergey_Shuklin — Sat, 08 Jun 2019 08:56:23 GMT

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.

topic How to Calculate PERCENTILE.EXC OF Excel in Qlikview in QlikView

How to Calculate PERCENTILE.EXC OF Excel in Qlikview

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