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 50th percentile. We can achieve this using the FirstSortedvalue()
and median()
functions.
=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= {'>$(=fractile(Value, 0.5))<=$(=fractile(Value, 0.75))'}>} Value))"}>} CaseID, -Value))', '$(=FirstSortedValue(distinct{<Value={"<=$(=Median({<Value= {'>$(=fractile(Value, 0.75))<=$(=fractile(Value, 1))'}>} Value))"}>} CaseID, -Value))' ), CaseID, Null() )
The calculated dimension for CaseID
gives us the claims corresponding to the median values in each quartile. As you can see, a Match()
function is being used to match the CaseID
with each of the four expressions within.
Let's decipher the first expression inside the Match()
function:
'$(=FirstSortedValue(distinct {<Value={"<=$(=Median({<Value={'>=$(=fractile(Value, 0))<=$(=fractile(Value, 0.25))'}>} Value))"}>} CaseID, -Value))'
The details of the expressions are as follows:
Median()
function then gives us the value which lies at the median of this rangeFirstSortedvalue()
returns the value of the output field (CaseID
) based on the sorted values of the value fieldIn situations where the number of claims in any quartile is an even number, there will be two claims which will correspond to the median values. In such a scenario, we want to select only the claim which is higher in the sorting order. Hence, we use a –Value
as the sort weight.
Similar to medians, we can derive the quartiles within quartiles using the Fractile()
function.