27 December 2022 Calculating fractiles in Qlik Share this message Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about calculating fractiles in Qlik. It seems like a lot of you have spend a great time around Christmas, since we are normally used to more answers. The correct answer is B Fractile Fractile is a function which allows you to calculate a fractile of a set of values in a field. A fractile is a value that divides a set of data into intervals. For example, the 50th fractile of a set of values would be the value that divides the set into two equal parts: the values below the 50th fractile and the values above it. The syntax for Fractile is as follows: Fractile([{SetExpression}] [DISTINCT] [TOTAL [<fld{, fld}>]] expr, fraction) To illustrate how fractiles work please see the diagram below: Here we can see that the 50th fractile is the exact median of the range. The first quartile will be the first 25% of values and so on for each quartile and the interesting to focus on is the deciles. This is all the values divided in parts of ten. So in order to get to that top 10% we need to use 0.9 as the fraction in the syntax. This will give us the value at the 90th percentile. Everything above this will be the top 10%. AGGR This brings us to the second part of the answer. To properly calculate the value of the 90th percentile, we need to use another aggregation. Fractile needs to be calculated on a dimension. In this case per person. So by using the Aggr function we first calculate the following (if you need to refreshen the Aggr function, please refer to this blog post): Aggr(Sum(Nice) - Sum(Naughty), Person) This calculates per person the score of each individual. Now it is possible to wrap this in the fractile expression to give the end result of the 90th fractile for all values of the score per person. Use cases Decile Analysis One very interesting feature is to do decile analysis. Decile analysis is a useful tool for understanding how a particular variable is distributed within a dataset and for identifying trends or patterns in the data. It is done by dividing the dataset in ten equal parts (deciles) and then calculate various statistics on these. It is often used in finance and economics to analyze income or wealth distribution. Below is an example of a decile analysis for sales per car brand: This gives great insights in revenue and which brands are responsible for the majority of revenue. This chart is made by using the following expression as a calculated dimension, so it it possible to also select the deciles. =Aggr(IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.1), 10,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.2), 9,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.3), 8,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.4), 7,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.5), 6,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.6), 5,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.7), 4,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.8), 3,IF(Sum(Revenue) <= Fractile(total Aggr(Sum(Revenue), brandName), 0.9), 2, 1))))))))), brandName) Other things to notice By default the fractile will be calculated over the used selections, you can use set expressions to change this. Use DISTINCT to calculate the fractiles over distinct values Use TOTAL to calculate fractiles over all possible values regarding the current selections. Nested calculations are not possible with use of Fractile() unless you use the Aggr() function. Fractile() is a chart function. In the script you can use FractileExc in combination with a Group By clause. That’s it for this week! More from the Bitmetric team Qlik Cloud Backup Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more. Masters Summit for Qlik The Masters Summit for Qlik provides the next step in your path to becoming a Qlik specialist. As an extra perk, Bitmetric has secured an over $450 discount just for you! Use code BITMETRIC at checkout. Read more here. Friday Qlik Test Prep Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 13 June 2025 Qlik Extension: Scroll to Object Ever since Qlik introduced extended sheets in June 2018, we at Bitmetric have been fans. The ability to extend a sheet vertically and add visualizations below the fold is a powerful way to present more insights without cluttering the main view. Yet, even after seven years, it still feels like a hidden feature. Many users […] Extension Qlik 2 June 2025 Simplifying Data Integration with TimeXtender At Bitmetric, we believe in making technology work for people, not the other way around. TimeXtender’s approach to data integration fits this philosophy by removing obstacles and giving users practical tools to manage their data. Solution TimeXtender 12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik
13 June 2025 Qlik Extension: Scroll to Object Ever since Qlik introduced extended sheets in June 2018, we at Bitmetric have been fans. The ability to extend a sheet vertically and add visualizations below the fold is a powerful way to present more insights without cluttering the main view. Yet, even after seven years, it still feels like a hidden feature. Many users […] Extension Qlik
2 June 2025 Simplifying Data Integration with TimeXtender At Bitmetric, we believe in making technology work for people, not the other way around. TimeXtender’s approach to data integration fits this philosophy by removing obstacles and giving users practical tools to manage their data. Solution TimeXtender
12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik