topic Re: Percentile calculation based on 2 columns: Value & Repetitions in App Development

Percentile calculation based on 2 columns: Value & Repetitions

fsomos — Sun, 13 Apr 2025 08:42:55 GMT

Hello. I load a table with 2 columns: Value and Repetitions:

Value	Repetitions
2	4
3	3
4	2
5	1
6	1
7	1
8	1

Therefore, value 2 is repeated 4 times, and so on... The above table represents this list of numbers:

2,2,2,2,3,3,3,4,4,5,6,7,8,

I want to calculate the percentile 80% for that list of values. I know, the result is: 5.6

How can, I get the result in a text box?. I tried:

But it's not working since I get:

And now 2222 it's considered as a full number and so on... Therefore, the 80% percentile is: 275.2 (Completely wrong!)

Please, any idea? It looks such a simple problem but... So difficult!!!! Please, help!

Thanks in advance!! 🙂

Re: Percentile calculation based on 2 columns: Value & Repetitions

Qlikard — Sun, 13 Apr 2025 12:58:41 GMT

Hey, is this what you're looking for?

Load script

TempTable: LOAD * INLINE [ Value, Repetitions 2, 4 3, 3 4, 2 5, 1 6, 1 7, 1 8, 1 ]; // Generate individual rows using IterNo() ExpandedData: LOAD Value RESIDENT TempTable WHILE IterNo() <= Repetitions;

This will expand each row based on its repetition count.

Then in your app (KPI, chart, etc), you can just use:

Fractile(Value, 0.8)

Let me know if that works for you!

Re: Percentile calculation based on 2 columns: Value & Repetitions

fsomos — Mon, 14 Apr 2025 06:40:19 GMT

Thanks for trying to help me. I appreaciate your approach! 🙏🙏 However, it's based on script. I need something based on expressions.

The example I sent was a very simple one. Reality is that I have tens of thousands of items, with values from 1 to 1000 (incremental steps of 1 unit). To make it simple, the loading table structure looks like:

Therefore, Item 1: 10, 30, 30, 30, 50, 50. Percentile 80%: 50

Item 2: 20, 20, 20, 20, 60, 60, 60, 60, 60, 60, 90. Percentile 80%: 60

Item 3: 20, 20, 40, 40, 40, 70, 100. Percentile 80%: 64

In the Script, I load the above table using "crosstable". Later I present a table where "Item" is the Dimension) and I need one column with the percentile for each item.

Doing it at script level would be unmanageable with so many thousands and thousands of resident tables (one resident tabla per item).

Any suggestion?