topic Re: Percentile in QlikView

Percentile

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

Hi Everyone,

I was wondering if anyone could help please.

I need to create a Qlik View file which calculates the Percentile of the values.

I have attached an Excel spreadsheet which shows the Percentile formulas. Basically if you filter on the Reference the Formula calculates what Percentage each rows value is against the overall for the year so far.

I would like Qlik View to work out this calculation for me, then I can create some buttons which will allow me to show 5%, 95% then everything in between i.e. 5% to 95%.

If anyone can advise me the best way to approach this I would much appreciate it.

Many Thanks

Kind Regards

Wendy

Re: Percentile

sunny_talwar — Tue, 21 Jun 2016 04:12:39 GMT

May be like this:

Table:

LOAD Ref,

Month(Date#(Month, 'MMMM')) as Month,

Value

FROM

[Test Data Percentile Result v2.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(Ref = Peek('Ref'),

If(Peek('Cum2') > 1,

If(Value = Previous(Value), RangeSum(Peek('Cum')), RangeSum(Peek('Cum2'),Peek('Cum'))),

If(Value = Previous(Value), Peek('Cum'), RangeSum(Peek('Cum'), 1))), 0) as Cum;

LOAD *,

If(Ref = Peek('Ref'), If(Value = Previous(Value), RangeSum(Peek('Cum2'), 1), 1), 1) as Cum2

Resident Table

Order By Ref, Value, Month;

Join (FinalTable)

LOAD Ref,

Max(Cum) as Div

Resident FinalTable

Group By Ref;

FinalFinalTable:

LOAD *,

Floor(Cum/Div, 0.001) as Percentile

Resident FinalTable;

Re: Percentile

Anonymous — Tue, 21 Jun 2016 11:16:36 GMT

Hi Sunny,

Thanks for your reply.

Is there anyway you could send me the Qlik View file as I am struggling to replicate it on mine.

Many Thanks

Wendy

Re: Percentile

sunny_talwar — Tue, 21 Jun 2016 12:35:43 GMT

Sample app attached

Re: Percentile

sunny_talwar — Tue, 21 Jun 2016 12:38:48 GMT

I have attached it now... I wanted to attach it earlier, but couldn't do it because of very slow internet

Re: Percentile

Anonymous — Tue, 21 Jun 2016 12:55:48 GMT

Hi Sunny,

Not sure if I'm missing something but I don't appear to have an attachment.

Kind Regards

Wendy

Re: Percentile

sunny_talwar — Tue, 21 Jun 2016 21:12:50 GMT

Open this link: Re: Percentile and you should see the attachment

Re: Percentile

Anonymous — Wed, 22 Jun 2016 11:23:46 GMT

Thanks Sunny,

That's great, and has saved me a lot of work.

Kind Regards

Wendy

Re: Percentile

Anonymous — Fri, 01 Jul 2016 11:27:09 GMT

Hi Sunny,

I am now trying to input the percentile solution into my data rather than the test data I sent to you and I am having some difficulty. The reason being is my value in the test data is made up with the following expression in my Straight chart. This expression gives me the Overall Average Amount (= Value in the Test Data).

Do you know how I could put this expression into my Script so that I could just call it Overall then it would make my calculation for the solution you provided me with much easier.

((Sum ({$<DocType= {'Invoice'}>}([L Amount]))

+Sum ({$<DocType= {'Invoice'}>}([Pi Amount]))

+Sum ({$<DocType= {'Invoice'}>}([Pa Amount]))

+Sum ({$<DocType= {'Invoice'}>}([Other Costs])))
-(Sum ({$<DocType= {'[Credit]'}>}([L Amount]))

+Sum ({$<DocType= {'[Credit]'}>}([Pi Amount]))

+Sum ({$<DocType= {'[Credit]'}>}([Pa Amount]))

+Sum ({$<DocType= {'[Credit]'}>}([O Costs]))))
/(Sum (Count))

Many Thanks

Kind Regards

Wendy

Re: Percentile

Anonymous — Fri, 01 Jul 2016 14:34:48 GMT

Hi Sunny,

I have managed to sort the previous expression by putting the following into my script.

Invoice Table:

[L Amount]+[Pi Amount]+[Pa Amount]+[Other Costs] as Overall

Credit Table:

[L Amount]+[Pi Amount]+[Pa Amount]+[O Costs] as Overall,

I have then used my straight table to divide by the overall Count of Reference to give my Overall Average.

However I am still having some issues with the Percentile table.

1. I have joined my Invoice Table to my Credit Table and created a [Doc Type] which is either Invoice or Credit. How do I now take off my credits from my invoices in the script so I can work out the Overall Average (which would be the Value in my original test data). The expression I have used to do this in the straight table is

(sum ({$<DocType= {'InvoiceID'}>}Overall)- sum ({$<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))

and this is the correct figure I am looking for. I'm just not sure how I convert this into the script instead of the Value which you have in the example.

2. In the test data I provided I gave you the months. However in my actual data I am working with actual days which my Master Calendar then converts to the months. The Script that you used in the example does not recognise my Month as the Master Calendar tab is after the Load Script. How do I convert my dates into months so that the script example works?

Any help you could give would be much appreciated.

Many Thanks

Kind Regards

Wendy

Re: Percentile

abhijith28 — Fri, 06 Sep 2019 07:41:05 GMT

Hi,

what changes has to be done, if i need the percentile of each value regardless of the Ref i.e. A and B.

Thank you,