Help needed to find 95th percentile for grouped data in qlikvew

[praveensprabhu]

Hello Qlik Wizards, 

I have a requirement where i need to find the 95th percentile on a dataset that is grouped. The data looks like this -

Date	Value	Count
15-07-2024	20	3
16-07-2024	30	2
17-07-2024	40	4
18-07-2024	10	1
19-07-2024	22	3

I want to calculate 95th percentile on the above data. Could any of you help me to solve this?

How did we arrive at the above data? See below table. for each data the count has been taken.

Date	Value
15-07-2024	20
15-07-2024	20
15-07-2024	20
16-07-2024	30
16-07-2024	30
17-07-2024	40
17-07-2024	40
17-07-2024	40
17-07-2024	40
18-07-2024	10
19-07-2024	22
19-07-2024	22
19-07-2024	22

[rwunderlich]

Do you want the 95th percentile on Value, Count or (Value * Count)?

-Rob

[praveensprabhu]

I need percentile on value. The data I have provided is grouped. calculating percentile on value column like Fractile(value,0.95) when data is not grouped is easy. But I want the percentile to be calculated on grouped value, where the values has been grouped for that particular day on the "count" no of times. 

[rwunderlich]

Perhaps

Aggr(Fractile(Min(Value), 0.95), Date)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[praveensprabhu]

Hi rwunderlich, 

I do not want to aggr on date. My data is already aggregated per day. In my above data set, "Count" is the no of times the "value" is grouped for one particular date. So for example, there were 3 rows of value 20 for the date 15-07-2024. Likewise for other dates. I have shown this in the 2nd table in my above question. 

[rwunderlich]

So you want the percentile calculated from the ungrouped data? Is this to be calculated in the script or chart?

Can you show what your expected result would be?

-Rob