Qlik Sense provides a host of statistical functions that can be put to effective use based on requirements in user reports. At a recent implementation, one of the requirements that popped out was to divide the data values into four quartiles. Quartiles are equivalent to percentiles which divide the data into four groups.
The first quartile is determined by every value which is equal to and less than the 25th percentile. The second quartile is determined by every value which is between the 25th and the 50th percentile. The third quartile is determined by every value which is between the 50th and the 75th percentile. The fourth quartile will be all the data values above and beyond the value of 75th percentile.
In order to generate quartiles in Qlik Sense, we make use of the Fractile()
function. The following recipe explains the process.
For the sake of this recipe we create a hypothetical situation and make use of an inline data load which gives a case level information for an insurance company. Load the following script in Qlik Sense:
Case: LOAD * INLINE [ CaseID,Value,Status 101,1500,Active 102,1800,Active 103,800,Closed 104,2590,Closed 105,3500,Closed 106,1200,Active 107,5600,Active 108,8000,Closed 109,5960,Closed 110,5000,Active 111,4000,Active 112,2500,Active ];
Quartile
:=If (Value <= Fractile (TOTAL Value, 0.25), 'Quartile 1', If (Value <= Fractile (TOTAL Value, 0.50), 'Quartile 2', If (Value <= Fractile (TOTAL Value, 0.75),'Quartile 3', 'Quartile 4')))
CaseID
.Value
:Sum(Value)
Value
to the top and sort it as numeric descending.CaseID
value is now grouped under the Quartile.The Fractile
()
function finds the value corresponding to the stated quartile in the range of the data values given by the expression. For example, a Fractile (TOTAL Value, 0.25)
works in the following way.
A value corresponding to the 25th percentile is calculated. The total qualifier disregards the chart dimensions.
In our calculated dimension, every CaseID
having the value below the 25th percentile mark is tagged as Quartile 1, between 25th and 50th as Quartile 2, and so on.
We can make use of a distinct qualifier inside the Fractile()
function. In such a case, only the unique values of the field Value
are evaluated.