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.
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-
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!
Okay, good news, I was wrong! @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?
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.