Calculating median in Report Studio

[raviahuja21]

I am trying to calculate median for the following set of data in report studio, however I am not getting the desired result.

I have the following in my chart  CalculatedTime as a measure and Facility in the series

I have filters of Organization in my report.

My database has the following record stored.

Orgid|Facility|CalculatedTime
1|Abc|1000
2|Abc|2000
1|Xyz|500
2|Xyz|200

The median that I expect for ABC is 1000+2000/2=1500
and the median for  XYZ should be 500+200/2=350

but unfortunately that is not what I am getting, I am getting the summed up value.

I have the following calculation for my median -> Median(CalculatedTime for Facility)


Any help would be appreciated.

Thanks
Ravi

[Lynn]

A median is not the same thing as an average (also called a mean).

You indicate a median function but your illustration shows an average calculation. If you had three values for facility Abc what value would you expect?

For example, say you had 1000, 1010, and 2000. The MEDIAN of these would be 1010. because the median is simply the number in the center that divides an ordered list equally in half. An AVERAGE of those same numbers would be 1336.667 because the average is computed as the sum of all values divided by the count of all the values.

Both are intended to show the central tendency of a set of numbers, but average can be more easily skewed by a very high (or low) value whereas mean is not.

Now that the math lesson is over you can figure out which function you need and also check the aggregation property of your data item.

You, along with a zillion other people who post on Cognoise, have failed to indicate if your source is relational or dimensional. If you care about getting the right advice it would make sense to provide that important piece of information  :)

[navissar]

Almost everything that Lynn wrote is absolutely true, and I concur completely.

Well, except, maybe, this:

Both are intended to show the central tendency of a set of numbers, but average can be more easily skewed by a very high (or low) value whereas mean is not.

I'm sure Lynn meant "whereas median is not". Just wanted to make sure this is absolutely clear.

[raviahuja21]

A median is not the same thing as an average (also called a mean).

You indicate a median function but your illustration shows an average calculation. If you had three values for facility Abc what value would you expect?

For example, say you had 1000, 1010, and 2000. The MEDIAN of these would be 1010. because the median is simply the number in the center that divides an ordered list equally in half. An AVERAGE of those same numbers would be 1336.667 because the average is computed as the sum of all values divided by the count of all the values.

Both are intended to show the central tendency of a set of numbers, but average can be more easily skewed by a very high (or low) value whereas mean is not.

Now that the math lesson is over you can figure out which function you need and also check the aggregation property of your data item.

You, along with a zillion other people who post on Cognoise, have failed to indicate if your source is relational or dimensional. If you care about getting the right advice it would make sense to provide that important piece of information  :)

Hi Lynn,

The source is relational and I am using Cognos 10.2.1.1, the calculation that I want is of median not of average, I had given the two values only for simple understanding.


Orgid|Facility|CalculatedTime
1   ABC   1000
2   abc   2000
3   ABC   500
4   ABC   450

In the above scenario the result should be 750 (since the no of elements are even it will calculate the median by arranging the numbers in asc order and taking the 2nd and the 3rd no which is 500+1000/2 ) as the median. however what i get is the sum value which is 3950

I have applied median and kept the rollup aggregate property as automatic, I have also tried various other combination but in vain.

Regards
Ravi

[Lynn]

Almost everything that Lynn wrote is absolutely true, and I concur completely.

Well, except, maybe, this:
I'm sure Lynn meant "whereas median is not". Just wanted to make sure this is absolutely clear.

You are correct Nimrod! I didn't mean mean...I did mean median. Isn't English wonderful sometimes?

Hi Lynn,

The source is relational and I am using Cognos 10.2.1.1, the calculation that I want is of median not of average, I had given the two values only for simple understanding.


Orgid|Facility|CalculatedTime
1   ABC   1000
2   abc   2000
3   ABC   500
4   ABC   450

In the above scenario the result should be 750 (since the no of elements are even it will calculate the median by arranging the numbers in asc order and taking the 2nd and the 3rd no which is 500+1000/2 ) as the median. however what i get is the sum value which is 3950

I have applied median and kept the rollup aggregate property as automatic, I have also tried various other combination but in vain.

Regards
Ravi

Not sure what various other combinations you have tried. Do you have facility as the scope specified in your "for" clause as indicated in the original post? Did you try setting the aggregate function to calculated or to None?

[raviahuja21]

Not sure what various other combinations you have tried. Do you have facility as the scope specified in your "for" clause as indicated in the original post? Did you try setting the aggregate function to calculated or to None?

Yes Lynn, I have it in my for clause, however the problem here is that when i take it in the list it shows me the correct results, it shows 750 across all 4 rows but when i take it for a crosstab or a chart it simply sums it up.