Using the FirstSortedValue() function to identify the median in a quartile range
Our next task is to find a claim corresponding to the median value in each quartile. A median is nothing but a value corresponding to the fiftieth percentile. We can achieve this using the FirstSortedvalue() and median() functions.
Getting ready
Continue with the same application as in the preceding recipe.
How to do it…
- Go to the Edit mode by clicking on .
- Select the table we created just now in the preceding recipe.
- Edit the
CaseIDdimension, and put in the following calculation:
=if(Match(CaseID, '$(=FirstSortedValue(distinct{<Value={"<=$(=Median({<Value=
{'>=$(=fractile(Value, 0))<=$(=Fractile(Value, 0.25))'}>}
Value))"}>} CaseID, -Value))', '$(=FirstSortedValue(distinct{<Value={"<=$(=Median({<Value=
{'>$(=fractile(Value, 0.25))<=$(=fractile(Value,
0.5))'}>} Value))"}>} CaseID, -Value))', '$(=FirstSortedValue(distinct{<Value={"<=$(=Median({<Value=
{'...
