Median calculation problem in Aggregate function

Question

Hi guys,

I'm using RM 5.3 and 6 versions and trying to come out with a median for my data (aggregated by an attribute value).

I realized the median calculation used is not correct. RM for both versions do not seem to take the average of 2 middle values if the number list is even.

As an example, use the following data and calculate median for DOM (aggregate by DATE):

DOM   DATE
33	537
47	537
49	537
57	537
79	537
91	537
102	537
123	537
133	537
134	537
149	537
155	537
186	537
238	537

The correct answer should be 112.5
RM gives the median as 102

Thanks!

Andrew2 · Answer

I could imagine it would turn into a complicated process with multiple aggregation groups. It's slightly more gymnastics time than I can spare at the moment but at a high level, I would use Loop Values for each aggregation group, filter the aggregated result for that value, do the ugly sorting thing and then store the result somewhere.

I don't know what would happen elsewhere regarding median calculations - we have to wait for one of those nice developers to say whilst we remain vigilant.

regards

Andrew

luqie · Answer

Thanks for the workaround Andrew. Ugly, but works. It gets abit unhelpful though if I have loads of values to aggregate in the same columns  (I only have one value for aggregation in the above example). Any suggestions for that?

Also, will other functions and charting be affected in the use of the engine's median function ?(eg k-medoids, boxplot etc)

Thanks!

Andrew2 · Answer

Hello Good find. Interestingly, if you sort the examples, the answer changes as in the attached. I suppose you could manually calculate the median you're after by using the two values - a bit ugly but it would work. regards Andrew