Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Hi,
I think Firm_Name should be a dimension? Or do I get something wrong?
- Ralf
Hi Ralf,
Dimension is Time_Period.
Can you upload sample data and the expected result?
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..
Please find attached solution. But the Median of Period2 = 55, so no match with a Firm!
- Ralf
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
can you please help on this?
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
If we can achieve this using set analysis, can you please assist me how to achieve this..?