Percentile

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar · Accepted solution]

Sample app attached

[sunny_talwar]

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

[Anonymous]

Hi Sunny,

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

Kind Regards

Wendy

[sunny_talwar]

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

[Anonymous]

Thanks Sunny,

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

Kind Regards

Wendy

[Anonymous]

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

[Anonymous]

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