topic Re: Calculating Percentile in QlikView

Calculating Percentile

suryaa30 — Thu, 03 Aug 2017 09:09:21 GMT

Hi I have created a chart as seen below

Expression used:

Count =Count({<Hypothesis_Flag={'1'}>}DISTINCT Poliza)

Count %=Count({<Hypothesis_Flag={'1'}>}DISTINCT Poliza)/Count({<Hypothesis_Flag={'1'},Monthly_Salary_Updated={"*"},Monthly_Salary_Updated={">0"}>} TOTAL DISTINCT Poliza)

Cumulative Count=RangeSum(Above(Count({<Hypothesis_Flag={'1'}>}DISTINCT Poliza),0,RowNo()))

Cumulative Count%=RangeSum(Above(Count({<Hypothesis_Flag={'1'}>}DISTINCT Poliza),0,RowNo()))/Count({<Hypothesis_Flag={'1'},Monthly_Salary_Updated={"*"},Monthly_Salary_Updated={">0"}>} TOTAL DISTINCT Poliza)

Please ignore the set expression part in above expression , it is simply count (Policy)/Count(TOTAL Policy) and calculation of accumulation using range sum

I want the expression to populate highlighted values in a text box or in a table. something like below. What is the count of policies that have salary greater than the 95th percentile and what is the 95 percentile of the salary. similarly for 96,97,98 and 99. Please note the values populated below are dummy.

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 09:14:19 GMT

may be for 95 percentile

Fractile(aggr(Count({<Hypothesis_Flag={'1'}>}DISTINCT Poliza),Salary),0.95)

Re: Calculating Percentile

suryaa30 — Thu, 03 Aug 2017 11:16:27 GMT

Thanks Kusha. I am not getting the desired results. Let me try the variations and get back to you.

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 11:29:35 GMT

Is your salary bucket is calculated dimension?

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 11:30:26 GMT

In my expression I have assumed that, Salary is your Salary Bucket which is created in the back end

Re: Calculating Percentile

sunny_talwar — Thu, 03 Aug 2017 11:33:33 GMT

Are you working with QV12.1 or above? If yes, then you might be able to use Aggr() function to do this

Recipe for a Pareto Analysis – Revisited

Re: Calculating Percentile

suryaa30 — Thu, 03 Aug 2017 11:36:16 GMT

Yes it is a calculated dimension.

=IF(Monthly_Salary_Updated>0,DUAL(Replace(Class(Monthly_Salary_Updated,$(vIncomeBucket)),'<= x <',' - '),Class(Monthly_Salary_Updated,$(vIncomeBucket))))

Re: Calculating Percentile

suryaa30 — Thu, 03 Aug 2017 11:37:57 GMT

It is version 11.2

Re: Calculating Percentile

sunny_talwar — Thu, 03 Aug 2017 11:39:07 GMT

You are going to have a tough time to achieve what you are looking for using QV11.2. Can you upgrade to QV12.1?

Re: Calculating Percentile

suryaa30 — Thu, 03 Aug 2017 11:40:48 GMT

Also is it possible to show my dimensions as 0-30K, 30K-35K instead of 0-30000,3 0000-35000.

My calculated dimension using bucketing is as below. The zeroes occupy too much space in graph

=IF(Monthly_Salary_Updated>0,DUAL(Replace(Class(Monthly_Salary_Updated,$(vIncomeBucket)),'<= x <',' - '),Class(Monthly_Salary_Updated,$(vIncomeBucket))))

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 11:50:34 GMT

=IF(Monthly_Salary_Updated>0,DUAL(Replace(Class(Monthly_Salary_Updated/1000,$(vIncomeBucket)),'<= x <',' - '),Class(Monthly_Salary_Updated/1000,$(vIncomeBucket)))) &'K'

in you input box, put 30000/1000

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 12:05:36 GMT

would you be able to share the sample?

Re: Calculating Percentile

Kushal_Chawda — Thu, 03 Aug 2017 12:05:53 GMT

Bhai, 12 me aisa kya he?

Re: Calculating Percentile

sunny_talwar — Thu, 03 Aug 2017 12:06:46 GMT

Sortable Aggr() function which can sort based on expression