You Have No Class: The Coolest Qlik Function You Aren’t Using

Featured Image - Ageing Class Function

What is the Class function?

The class function allows us to automatically group the values of a field into equal-sized ranges. So if we have a list of people and their ages, we can quickly sort them into groups based on their age, like 0 to 10, 11 to 20, and so on. The syntax of the class function is pretty straight forward although its implementation (as we will see below) takes a little more thought. The first parameter is the field or expression we are separating into ranges. Note that this must resolve to a number. The second parameter is the size of the bucket. This can be driven by a variable. The 24optional third parameter will replace the “x” in the label and the final optional parameter will offset the buckets. Class Syntax 1 This post will explore the use cases of this function with some practical examples and tips.

Why Do We Need Class?

The primary use of the class function is to facilitate the creation of Histograms in our dashboards. If you are unfamiliar with a histogram you could look on Wikipedia for a definition but I much prefer the definition found on Dictionary.com:

Histogram: a graph of frequency distribution in which rectangles with bases on the horizontal axis are given equal widths equal to the class intervals and heights equal to the corresponding frequencies.

So, visually a histogram is a bar chart. But a histogram is really a special bar chart. Comparison of Normal Bar Chart and Histogram Here is a Histogram done in Excel. Excel Histogram Example Let’s do a few examples of histograms in QlikView to illustrate the class function and it’s use. Note that the same syntax can also be used in Qlik Sense. In both scenarios, the data was just dummied up from a couple 2-column Excel worksheets.

Class Example 1 – Number of People by Age

The first example is pretty simple. We want a histogram (bar chart) that shows the number of people in my data segregated by age buckets: one bucket for every ten years. Where do we need our class function? Normally our efforts are focused on the expression dialogs, but the class function affects our dimension. So, I create a bar chart and instead of pushing one of our data dimensions over, I instead add a calculated dimension. In this case I am telling the class function to create ranges across the values found in the Age field and to separate the Ages into buckets of 10 years. You might notice also, that I nested the function into a replace function. I really don’t care for the default “10 <= x < 20” labels. So I replaced that whole mechanism with a simple dash. Class Syntax Example 1 The height of the bar is controlled by our expression. We are counting the number of people. Expression Example 1 And here is our chart. Class Function Example Chart by Age Ranges Note that with a simple example like this where we simply referencing a field, you could use the class function in the script, creating the Age Range field there and then using it as a regular dimension in the chart. Another cool use case of this would be to create a list box for the Age Range. When creating the object, instead of using a pre-defined dimension, scroll all the way down and select <Expression>. Then insert the same function and we now have a convenient way to select a group of ages. Class Function used in a List Box

Class Example 2 – Number of Customers by Sales

Our second example takes things to a more complex level. I want a histogram that groups my customers by their total sales. So Customers that purchased from 50,000 to 60,000 will be grouped together in one bucket. In this example, it would not be possible to create this class function in the script without some complicated acrobatics. So we will focus again on the calculated dimension option. Note that in the first parameter, instead of referring to a field like in the Age example, here we are grouping the customers by the accumulated sales for each. That requires us to do an expression to find that amount for each customer before creating the class ranges. Class Function Example 2 a The expression in this chart will again be very simple. We will count up the customers. In this case we will use a distinct qualifier since it is possible for a customer to have multiple records in my Sales table. Expression for Example 2 Here is the chart but I can see there is an issue. The ranges were created, but they are out of order. Chart for Example 2 - The dimension axis is not sorted correctly Let’s make an adjustment to the calculated dimension so that QlikView can sort it appropriately. We use the dual function to tell QlikView that the first part of the expression will be used for labeling and the second part of the expression will refer to a number for sorting purposes. All I did on the second part of the formula is copy and paste the first part and remove the replace function which was turning our values into strings. You might go to the sorting tab and make sure you have checked the box for sorting the dimension numerically ascending. Class Function Example 2 b Here is the finished chart. Final Chart - Sorted Correctly Now I want to extend this a little further. It would be great if we could allow our users to create their own buckets. I created a variable called vBucket and then added a slider to the sheet to give the user a way to adjust the value of the variable. Then instead of using a hard value in the second parameter of our class function, I replace it with the variable name within a dollar sign expansion. Class Function Syntax with a Variable Looking at the chart once more, this will give the user tremendous flexibility on how the chart groups the customers. Dynamic Ranges Chart for Class Function

Final Thoughts

Because the class function might be a little obscure and requires the use of calculated dimensions, I think developers shy away from its use. But as you can see from the above examples, it is really not that hard to implement this to create charts that are very usable and necessary in many implementations. Do you have other use cases for the class function? I would love to hear about them in the comments section below. Happy Qliking!

3 comments… add one
  • Reply Johan Hessler January 31, 2017, 10:24 am

    I would love a format parameter to the class() function so you don’t have to do all these replace / subfields to get the desired result. Its just stupid you’re forced to do a dual() just because you need to do a replace() function and breake the dual func. of the class function.

    For simple cases where only the part before the X should be displayed the class() function can be “prefixed” with a num() (as the class() is a dual function)

  • Reply Aaron Couron February 2, 2017, 1:38 pm

    Johan,
    I agree on both points. It definitely would be nice to get a format parameter for middle part of the display. And the idea of referencing the first number as your sort parameter is just intuitive. Good ideas for the R&D department!
    Thanks for the comment.

  • Reply Cotiso Hanganu February 10, 2017, 7:31 am

    Hi Aaron,

    Pretty clear example. I completely agree on the great need to master class function.

    For he ones that need an extra mile to what you’ve already shown, I suggest to have a look to our small (but mighty ! 😉 blog post: http://qqinfo.ro/en/creating-exponential-buckets-in-qlikview-charts/

    Regards !

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons