Access to "median" function for table aggregations and analytics?

Product Suggestions
1

It doesn’t look like there is a median function in either Table Aggregations or Table analytics…

What I’m trying to accomplish is trying to understand the shape of a distribution by comparing the average to the median. If they differ significantly, that potentially tells me a lot about the distribution.

If the median is much lower than the average, then I know that the average is mostly being driven by old orders.

If the median is much higher than the average, then I know that the average is mostly being driven by new orders.

My current idea is to subtract one from the other (haven’t decided what order) to create an “index” KPI.

If it matters we’re still on LTS8 but should soon be updating to LTS9.

2

Hi @Richard-SNN
You have good timing, we have been discussing this topic internally lately! This is something we would like to add, and hopefully soon. However, we are still working though this in context of some larger changes, so the timeline of adding this is not perfectly clear as of yet.
-Pete-

3

Hey Pete, just checking in to see if this has made it into the roadmap!

4

Hi Richard,

This one has been on backlog for a while. I will poke the team on it, but since it is not as highly sought after of a feature, it may continue to be lower priority. Feel free to rally any troops to vote and comment on this if you / others in the community want to bump the priority!

5

Yeah, not a high priority but I was also hoping it was a really small lift so it would make it in sooner or later :slight_smile:

6

Okay, good news, I was wrong! :slight_smile: @canaalpaslan let me know that median is now available within operations in analytics! I’m note entirely sure which LTS this came out on, but do you see this available in your release version?

image
image2172×866 123 KB

Median is still not available in aggregations in tables, however.

1 Like
7

For posterity…

If you need to use the Median as a table aggregation, you can get the unique values ID aggregation and the count aggregation of your query. Then use trigger logic to get the index of the Unique Values aggregation equal to FLOOR(Count Aggregation / 2).

The secret here is that the unique values aggregation actually comes in sorted based on how you defined your query sort! So however you sort your query will define how you calculate the median.

Note-- This could also work for getting the Nth percentile.
Note-- As always, queries are limited to 1000 records (unless you use the agg API) so just be careful if that is a possibility.

2 Likes