Mode Function

[Jennell_Yorkman]

Have you ever heard of the Mode function?  Until recently, I was not aware of this function.  The Mode function can be used in the script or in a chart to find the most commonly-occurring value in aggregated data.  I will show you how it works by loading the small data set below.

Now that I have loaded this data, I may want to see which Product Category or Product Subcategory occurs the most.  To do this in Qlik Sense, I can add a Text & image object to a sheet and add the measure Mode([Product Category]) or Mode([Product Subcategory]).

• Mode([Product Category]) returns Clothing because it is the most common value in the Product Category field.
• Mode([Product Subcategory]) returns Jerseys because it is the most common value in the Product Subcategory field.

Making selections can affect the data returned by the Mode function.  For instance, if I selection Store A, Accessories will be the most common product category and Helmets will be the most common product subcategory.  If I want to avoid these values changing when I make selections, I can simply use the set analysis expression {1} which will evaluate all records and ignore any selections I have made.

• Mode({1} [Product Category])
• Mode({1} [Product Subcategory])

The Mode function can also be used in the script to determine the most commonly-occurring value.  The key to remember when using the Mode function in the script is to use the Group By clause.  By using the Group By clause, you are indicating what field the values should be aggregated by.  In the script below, I am loading the products in the first load script and then in the second load script, I am using the Mode function to determine the most commonly-occurring store by Product Category.

The results will look like this:

For the Accessories product category, Store A is the most common and for Clothing, Store C is most common.  If there is not one single value that occurs more than the others, then the Mode function will return NULL.

The Mode function provides a quick way to see which value occurs the most in aggregated data.  It can be used with text data, as done in the examples show here, or with numeric data.  Hopefully, you learned something new as I did about one of the many functions offered in Qlik Sense and QlikView.

Thanks,

Jennell

[Oleg_Troyansky]

Very nice article about this little-known function, thanks!

[MK_QSL]

I was also not aware about this function before reading this article... Thanks for this blog !

[#4]

Nice article.

[evan_kurowski]

Anyone who doesn't love the mode function is just mean.

[selcukcadir]

Very nice article

[prieper]

It may be worth to add that MODE does not deliver a result, if there are more than 1 identical occurances of the corresponding dimension. Undocumented (and consequently highlighted as error in the syntax-checker) you may work with MODE(DISTINCT MyDimension)

This forces QV to deliver always a result (guess the most frequent one by loadorder)

edit: apparently wrong statement - sorry.

Have Fun! Peter

[JonasValleskog]

Peter, I'm not quite sure how you've managed to get MODE(DISTINCT fieldName) to return anything at all - it sounds like a bug to me. DISTINCT where used removes any duplicates prior to aggregation and since the MODE function is looking for the most frequently occurring value, duplicates is what has to be counted. So, Mode(DISTINCT ...) of anything at all should reasonably not return anything - or if the function supported returning multiple results, all field values ought to be returned as they are all as commonly occurring as each other if they all occur exactly one time (i.e. they are distinct).

If you can reproduce your scenario where mode(DISTINCT ...) returns anything, by all means share - would be interesting to take a look at what is going on.

Cheers

Jonas

[Anders_Eriksson]

Peter, your explanation of Mode(DISTINCT ...) does not make sense.

From a quick test of Mode(DISTINCT ...) I would say that the DISTINCT modifier
makes Mode() return a value when there are just one possible value in the dataset.

From Jenells example when nothing is selected Mode(DISTINCT [Product Category])
returns Null since there are more than one possible [Product Category].

If you select "Store C" it returns "Clothing".

Appears it works like the Only(...) function!

The DISTINCT modifier is documented in the Help-file but it does not say what it does.

The ordinary function of the DISTINCT modifier does not make sense together with the Mode() function.

Like Jonas says DISTINCT removes duplicates of the values so that you have a reduced list of values where each value occurs only once. Finding the most common value when all the values occurs once is only possible when there is only one value, hence the Mode(DISTINCT ...) works like Only(...).

[prieper]

@ Anders and Jonas:

I cannot reproduce same and have to admit, that I made above comment without testing prior.

Thanks your feedback.

Peter

[cheburashka]

Nice article. Though I did not yet have a usecase for this function.