Calculate Median in TM1

[mastertito4]

Am I missing something, I have been looking for a while now and can't seen to find any way to calculate a median in TM1. Is it simply not part of it's functionality?

[LoadzaGrunt]

Statistical functions aren't well catered for in TM1, and there isn't a rules or TI function to find median averages. There is a MEDIAN MDX function that I guess you could try and leverage in either a dynamic subset or view query (via a 3rd party tool).

Which would lead me to ask: do you want to filter datasets or metadata (dimension elements) that point to that data ? E.g. is your issue similar to 'show me the list of school children who have the median age in the school' (metadata filtering) or is it 'tell me the median age of from the available data on childrens ages' (data filtering) ?

[LoadzaGrunt]

As an example...

Original cubeview:


MDX query:


Result:

[mastertito4]

Thanks for the help, ended up just using an average instead of a median for simplicity,
I figured that the extra time and effort were not worth the benefits at this point

[trentban]

just bumping this thread in hope there has been any solutions since. I'm surprised there hasn't been many questions on median and mean in TM1.

ConsolidateMedian() function would be nice.

My example is getting a median salary over the org dimension. Yes could use average but in this instance the requirement is median.

I'm not sure if i've dug myself into a deeper hole thinking about this but to work out the median at C level in a TI, i've had to;

1 - Import median salaries from the source into the n level org dimension elements as a string. For the other dimensions, import into both "not assigned" elements and the Top Consolidation element.
2.0 - Cycle through all org dimension consolidation levels
2.1 - Create a temporary dimension with the salary values, padding them with leading zero's so it can be sorted correctly
2.2 - Create subsets for each org consolidation level so the elements(salaries) are numerically sorted
2.3 - Work out the middle index using SubsetGetSize. Divide by 2 and round.
2.4 - If the middle index is odd, easy just SubsetGetElementName and thats your median, otherwise if the index is an even number, also SubsetGetElementName for the next index found in the above step and average them for the median
2.5 - Amongst all that there is a bit of converting to Number and back to string to remove the leading zero's added earlier
2.6 - Populate median value into the string measure at the c level elements in the org dimension and Not Assigned elements for other dimensions
2.7 - Populate median value into the string measure at the c level elements in the org dimension and Top Consolidation level elements for other dimensions
2.8 - I also have a numeric measure which i can now add a rule to point to the string element and make it a number

Not very user-friendly as the median will only show in Not Assigned and total consolidations for other dimensions but i was at the point where anything will do. And this gives me the median for all org consolidation elements including alternate hierarchies and consolidation that only exist in TM1.

surely there is something easier

[lotsaram]

Hi Trent,

What you are describing in terms of the calculation mechanics of creating an ordered subset and picking the midpoint is the only way currently to do this and it sounds pretty reasonable. (I'm pretty sure we did something very similar with the merch KPI cubes at you know where, just it was with averages as we didn't yet have ConsolidatedAvg or ConsolidatedCount in 9.4).

The only thing I would be wary of is the string measures. I don't think this would really have any performance issues in the cube itself as the string to numeric conversion is trivial. But if the cube is large then having a string measure will really limit the ability to optimize the dimension order which could lead to more memory consumption and slower overall performance than without this restriction. (My rule of thumb is it is worth optimizing a cube if it is > 1GB). The alternative would be rather than using the string measure to store the median to pull the value from a separate cube which is either flat or has a lot less dimensions.

ConsolidateMedian() function would be nice.

Agree 100%. Although the function would need to have some smarts as returning the median of populated leaf cell values won't cut it as business requirements will always dictate that as you move up consolidation levels then the basis for the 1-dimensional array or list of the values for the median calculation will change so the function would need to allow for this otherwise you would still need workarounds.

[trentban]

Thanks Lotsaram
Yes, i'm only working with a small cube, tiny compared to those other ones at the other place

cheers

[David Usherwood]

Suggest you look at ykud's interesting post on using (clones of) Excel functions in TM1, via the Java Extensions feature:
http://ykud.com/blog/cognos/tm1-cognos/ ... q-and-more