nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to calculate percentile (90th) in a table transformer?

Green, Desmond

How do I calculate the 90th percentile of a series of numbers in a table, using a table transformer macro?

Let's say I want the 90th percentile of the values in the 'Elapsed Time' column of a table. What's would be the SQL and easiest way to do this, as I can't see any dedicated functions for this?

Accepted answers

Stiltsoft support

Hi @Green, Desmond ,

Think you need smth like a PECENTILE function in Excel. So you may take the Table Spreadsheet or Spreadsheet from Table (if your source table is a reused one or macro generated) macros and look into the PERCENTILE_INC and PERCENTILE_EXC functions.

All comments

Stiltsoft support

Hi @Green, Desmond ,

Green, Desmond

That could work.

I created a Table Spreadsheet and tried to do some logic to filter rows where the value in column F=1, and to then work out the 90th percentile of those qualifying rows. I'll want to do similar where the value of F=2, or 3 or 4. But one thing at a time.

I have that first calculation working in Excel, as:

=PERCENTILE.INC(IF(F:F=1,K:K),0.9)

If I try it in Confluence as:

=PERCENTILE_INC(IF(F:F=1,K:K),0.9)

the I get a #VALUE! error, and also the same when I try and avoid the headers with:

=PERCENTILE_INC(IF(F2:F1000=1,K2:K1000),0.9)

What do you suggest?

Stiltsoft support

We can suggest splitting your functions.

Firstly, you may filter out your rows with F=1, F=2 and so on:

=IF(F1=1,,K1)

If F=1, then an empty cell will be set. If F is not 1, then the according value from the K column will be taken. You copy this function across the whole column.

Then for each of these new columns (with F=1, 2, 3 and so on) you may apply the PERCENTILE_INC function.

Green, Desmond

I can use a Table Spreadsheet and then a Table Spreadsheet Include to report on a specifc range for the 90th percentile values I'll have worked out, through the use of helper columns etc.

All seems a bit messy and I try to use a table filter or table transformer and the summary data has gridlines and formatting issues.

Is there no way to work out percentiles through some SQL?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-spreadsheet

table-filter-charts-spreadsheets