Median Calculation

[PradeepReddy]

Hi,

I had a simple requirement, but not getting the solution. May be missing something..

Scenario:

There is 3 fields in one table.(Fields: Firm_Name, Time_Period, Hours). I want to calculate the Median of the Hours and correponding Firm_Name.

Dimension : Time_Period

Expressions: Median, Firm_Name( corresponding to the Median value)

Functionality:

If the count is Odd number then there is no issue.--- Median(Hours)

If the count is Even then, the median value doesn't match to any existing Hours value. So I am not able to display the Firm_Name. So I want to  dispaly the middle 2 values and its correposnding Firm_Name.

Ex: 6,9,2,5,10,7 (After sorting... 2,5,6,7,9,10)

     Here I want to display the values 6 & 7 and its coresponding Firm_Name.

Thanks in advance..

[rbecher]

Hi,

I think Firm_Name should be a dimension? Or do I get something wrong?

- Ralf

[PradeepReddy]

Hi Ralf,

Dimension is Time_Period.

[rbecher]

Can you upload sample data and the expected result?

[PradeepReddy]

Here is the sample data...

Firm_Name	Hours	Time_Period
Firm1	12	Period1
Firm2	10	Period1
Firm4	14	Period1
Firm9	45	Period2
Firm10	65	Period2
Firm11	33	Period2
Firm15	78	Period2
Firm12	87	Period2
Firm18	45	Period2
Firm19	70	Period3
Firm22	55	Period3
Firm23	76	Period3

Expected output would be like below...

Time_Period	Median	Firm Name
Period1	12	Firm1
Period2	65 78	Firm10 Firm15
Period3	70	Firm19

I am getting the result, If the count is Odd number. (using Median(), FieldIndex(), FieldValue()..)

But not getting for the Even count....

Thanks in advance..

[rbecher]

Please find attached solution. But the Median of Period2 = 55, so no match with a Firm!

- Ralf

[PradeepReddy]

Ralf,

thanks for your quick support....

yes you are right... that is the tricky one

IF Count(Hours) is odd , then it is straight forward.. no issue in this case...

    (in this case Median of Period2 should exists exists in the list of hours)

IF Count(Hours) is even, then we need to display the  Firm_Name of middle 2 values... as shown above.

    (in this case Median of Period2 may or may not exists in the list of hours)

Regards,

Pradeep

[PradeepReddy]

Jagan Mohan

can you please help on this?

[rbecher]

Hi,

this is what I have so far. But it's not working for the upper middle (Period2=65). I think this is a set analysis isssue..

- Ralf

[PradeepReddy]

If we can achieve this  using set analysis, can you please assist me how to achieve this..?