Counter Functions

Counter functions count records within a table to return results. Some do not require any input values at all.

This article will cover the basic principles of working with counter functions, including:

  • The general function syntax
  • Working with counter functions in the data load editor
  • Working with counter functions in charts

For a complete, up-to-date list of counter functions, please see the Qlik Sense help pages here.

The counter function syntax

The function syntax can differ massively depending on the function. For up-to-date syntax, please take a look at the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

Unfortunately, it isn’t easy to decipher the syntax provided in the help pages linked above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages.

To explain the syntax, we will use a couple of different functions that have a few different syntax elements and decipher them in turn.

Example 1: IterNo

This function returns the count of how many times a record has been evaluated when the LOAD statement contains a ‘while’ clause. Note that the function doesn’t require any inputs but parenthesis are still required.

Copy code 


IterNo()

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. As there are no parameters in brackets, you do not need to enter any when using this function. You will still need to include parenthesis for the function to work

An example of using this function:

Copy code 


CALENDAR:
LOAD
   IterNo() as ID,
   Date(From + IterNo() - 1 ) as Date

While From + IterNo() - 1 <= To;

LOAD * INLINE [
    From, To
    01/01/2022, 31/12/2022
];

The above code will create a table containing daily dates for the year 2022.

Example 2: RowNo

This function returns the number of records in the resulting Qlik table. Note that the counter starts at 1.

Copy code 


RowNo([TOTAL])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. Any parameters in square brackets are optional. In this function, [TOTAL] is the only parameter and it is optional. The word ‘TOTAL’ is a function qualifier. It is applicable to use of this function in a chart expression only.
  3. When not using the optional parameter, the function still requires parenthesis to work.

An example of this function in action could be:

Copy code 


RowNo()

Each row of data in the table will be given a number, starting from 1.

How to use counter functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use counter functions in the load. The rules are:

  • The IterNo function can only be used with a ‘while’ clause in the LOAD statement.
  • Some counter functions can be used in the ‘where’ clause of the LOAD statement to filter the data but be mindful of result differences between RecNo() and RowNo() specifically.
  • Be mindful of the differences between RecNo() and RowNo() when your data contains a where clause that limits records loaded. The function RecNo() gives a number to each row of the INPUT table, whereas RowNo() gives a number to each row of the OUTPUT table.
  • You can use the ‘order by’ clause in the LOAD statement if you need your counting to start and end at specific values.

A very basic load may look something like this:

Copy code 


TABLE:
LOAD
   "Customer Name",
   "Total Sales",
   RowNo() as "Customer Rank"

FROM [my_data.qvd] (qvd)

ORDER BY
   "Total Sales" desc;

The loaded table will rank customers 1-n based on what their total sales are, largest to smallest.

How to use counter functions in visualisations

Only one counter function can be used in visualisations. This is the RowNo() function. This function returns the position of the row in the table. The results can be per dimension or per total rows in the table, depending on the function qualifier used. Note that RowNo() must be used as a measure, not a dimension.

Here is an example of the function being used to return the row number within a table containing customer orders per region and in total.