en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Chris Richard  
#1 Posted : Friday, January 10, 2014 4:28:20 PM(UTC)
Chris Richard
Rank: Newbie

Groups: extranet\Forum
Joined: 12/18/2013(UTC)
Posts: 4

Is there a formula to calculate the Median value from a data set?

As a work-around, I have created Columns to calculate an index with the "Count" formula.

Thanks,

Chris
Niels Thomsen  
#2 Posted : Wednesday, January 15, 2014 6:30:14 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Hi Chris

No unfortunately there is no bulit-in median function in the TARGIT client. However it is possible to find the median using the existing functions with a little work.
I have created an example here:

1. I have a crosstab showing the sales results of a number of sales guys – the numbers are sorted ascendingly
UserPostedImage

2. I want to find the middle observation – median – so I do a calculated column with a count of all the dimension members from the top
count(d1,d1:0,m1)
– this gives me a column with numbers to identify each observation (sales result)
UserPostedImage

3. The median is the middle observation – but if the numbers of observations is even – the median is actually the mean of the 2 middle observations – so I have to find out if I have an even or odd number of observations – formula:
mod(max(c1, all, m1);2)
- this return 1 for odd numbers and 0 for even
UserPostedImage

4. I need to find the middle observation or middle 2 observations depending on whether I have an odd or even number of observation – new calculation:
if sum(c2, 0, m1) = 1 then (max(c1, all, m1) + 1) / 2 else (max(c1, all, m1) + 2) / 2
– this will get number of the median observation. I f the number of observations is even – it will get the second of the 2 middle observations
UserPostedImage

5. Now to calculate the median for an odd number of observations – yet another calculation:
if count(d1, d1:0, m1) = sum(c3, 0, m1) then sum(d1, 0, m1) else 0
In this case however this is not true since we have an even number of observations – so…
UserPostedImage

6. …if there is an even number of observations:
if count(d1, d1:0, m1) = sum(c3, 0, m1) then (sum(d1, 0, m1) + sum(d1, -1, m1)) / 2 else 0
This takes the mean of the observation I counted my way to plus the previous observation
UserPostedImage

7. Finally I’m home – I just need one number which is the median – so depending on whether we have an odd or even number of observations:
if sum(c2, 0, m1) = 0 then sum(c5, 0, m1) else sum(c4, 0, m1)

Hiding all the intermediates – it will end up like this:
UserPostedImage

If someone on the forum has an easier way to get there - don't hesitate to post it :-)

Hope it makes sense.


Edited by user Wednesday, January 15, 2014 6:47:20 PM(UTC)  | Reason: Not specified

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Páll Bjarkason  
#3 Posted : Wednesday, April 13, 2016 4:03:37 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 154

Hi Niels

I am not sure whether it is easier, but it is possible to keep the dimension order when calculating the median.
In some cases an unsorted dimension is preferred, e.g. when portraying calendar months, hence the solution to the problem is slightly different.


UserPostedImage


It seems to check out that medianLow and medianHigh are rowNumber 6 and 7.

Note: Using the AlwaysOne ensures the presence of 12 moths (dimension members) in the year, and is not subject to the occurrence of (past or future) gaps in data.
In this case, the numbers of observations is always even and there is no need for the mod operator.
Read more on AlwaysOne here.

A more generic approach would be to implement a measure in MDX.

Xview code snippet:
<measureCalculations>
<formula>
<title>minNumber1</title>
<expression>min(d1, all, m1)</expression>
<color>6FB121</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber1Post</title>
<expression>if sum(d1, 0, m1) = sum(d1, 0, m2) then max(d1, all, m1) else sum(d1, 0, m1)</expression>
<color>3AAADC</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber2</title>
<expression>min(d1, all, m3)</expression>
<color>E42322</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber2Post</title>
<expression>if sum(d1, 0, m3) = sum(d1, 0, m4) then max(d1, all, m1) else sum(d1, 0, m3)</expression>
<color>019A9A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber3</title>
<expression>min(d1, all, m5)</expression>
<color>E7511E</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber3Post</title>
<expression>if sum(d1, 0, m5) = sum(d1, 0, m6) then max(d1, all, m1) else sum(d1, 0, m5)</expression>
<color>EC80AD</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber4</title>
<expression>min(d1, all, m7)</expression>
<color>016B6A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber4Post</title>
<expression>if sum(d1, 0, m7) = sum(d1, 0, m8) then max(d1, all, m1) else sum(d1, 0, m7)</expression>
<color>97BE0D</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber5</title>
<expression>min(d1, all, m9)</expression>
<color>F29400</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>minNumber5Post</title>
<expression>if sum(d1, 0, m9) = sum(d1, 0, m10) then max(d1, all, m1) else sum(d1, 0, m9)</expression>
<color>00487D</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
</formula>
<formula>
<title>MedianLow</title>
<expression>min(d1, all, m11)</expression>
<color>F5BDD5</color>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
<misc>
<item name="GraphType">L</item>
</misc>
</formula>
<formula>
<title>MedianLowPost</title>
<expression>if sum(d1, 0, m11) = sum(d1, 0, m12) then max(d1, all, m1) else sum(d1, 0, m11)</expression>
<color>94C36A</color>
<hidden>true</hidden>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="true" total="false"/>
</formula>
<formula>
<title>MedianHigh</title>
<expression>min(d1, all, m13)</expression>
<color>8698C8</color>
<format reversesign="false" astime="false" abbreviatebmk="false">Standard</format>
<label visible="true" measure="false" dimension="false" percent="false" value="false" total="false"/>
<misc>
<item name="GraphType">L</item>
</misc>
</formula>
</measureCalculations>

Edited by user Wednesday, April 13, 2016 4:16:38 PM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error