Median of non-integer values

[Anonymous]

Hi Community,

I need to find the median of the Grade Column, which in this case will be 6+. I also have to apply certain filters, prior to  calculate the median. So I am using the expression:

Median
({<[concat] = {"=rank(Count([concat]))=1"},
[Template]= {"=rank(Count(Template))=1"},
[Date]  ={">=$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"}

>} Grade) .

The problem with the above expression is that it doesn't work for non-integers. Is there a way wherein instead of taking Median of Grade, I will take Median of ID associated with the grade and than put the result in variable.

Than I can get the corresponding Grade using that variable.

The 2nd approach I think is to generate the RowNo dynamically in the set analysis and then use it to get the median Grade. But I am not sure how to generate the RowNo dynamically in setanalysis.

Need urgent help on this.

Date	Template	Concat	Grade
2011-11-30 00:00:00	General Industries	Net Sales	6
2012-01-31 00:00:00	General Industries	Net Sales	7
2015-03-31 00:00:00	General Industries	Net Sales	6+
2014-03-31 00:00:00	General Industries	Net Sales	6+
2015-03-31 00:00:00	General Industries	Net Sales	6+
2015-03-31 00:00:00	General Industries	Net Sales	6+
2014-08-31 00:00:00	Middle Market	EBITDA	5

[Or]

Fayez,

Assuming your full list of values reflects the one shown here, why not simply replace any instance of '+' with '.5' - so 6+ is instead 6.5 - and then calculate the median as a number (any number will do, not just integers)? If your data is a little more complicated, you can still use e.g.

if(Right(Grade,1)='+',left(Grade,1)+0.5,if(Right(Grade,1)='-',left(Grade,1)-0.5)) as NumericGrade;

Essentially, if you want to find a median, the most logical way is to convert your data to numbers.

[sunny_talwar]

Did you get a chance to look at the approach provided here?

Get the middle number or median

[Anonymous]

That approach is working only when the count of grades in the filtered dataset is odd.

In case of Even count it does not work because it calculates the median of 2 values in NEW Field.

[sunny_talwar]

What do you expect when having even number of observations?

[Anonymous]

I did some analysis on the approach you mentioned. The issue with it is that when I apply ,Only({<NewField = {"$(=Median(NewField))"}>}[FBI Grade (Fac Own)]), it returns me the median of entire dataset. So for example if I use

Only({<[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-24),'MM-DD-YYYY')))<$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"}, 
NewField = {"$(=Median(NewField))"}>}FBI_Grade)

I don't get correct grades.

I want it to apply median of the filtered data that I get using:

({<[FBI Financial Statement Date (Fac Own)]  ={">=$(=(date(AddMonths(Updated_Date,-24),'MM-DD-YYYY')))<$(=(date(AddMonths(Updated_Date,-12),'MM-DD-YYYY')))"},
[FBI Template Name (Fac Own)]= {"=rank(Count([FBI Template Name (Fac Own)]))=1"},
[Concat_FBI_FinancialRatio] = {"=rank(Count([Concat_FBI_FinancialRatio]))=1"}

So I am filtering my data to some date range, rankings of template and concat field. Once I have this data I want to get the median grade.

And incase of even count I should show middle two grades.

Hope I am able to put is clearly

[Anonymous]

I got the other part figured out. Please help me with the even count of grades

[sunny_talwar]

What would be the median here?

1-,1,1+,2-,2,2+,3-,3,3+,4-

[Anonymous]

Considering this as sort order it should be 2,2+

[sunny_talwar · Accepted solution]

Check this out:

Expression:

=Concat({<NewField = {"$(='>' & (Median(NewField) - 0.126) & '<' & (Median(NewField) + 0.126))"}>}[FBI Grade (Fac Own)], ', ')