Conditional Functions

Conditional functions are functions that evaluate specified conditions and return a result based on the outcome of the condition evaluation.

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

  • The general function syntax
  • Working with conditional functions in the data load editor
  • Working with conditional functions in charts
  • Combining conditional functions with other functions

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

The conditional 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: Alt

This function tests the specified input expressions and returns the result of the first one that yields a valid numerical value. An alternative value is set to be used when none of the expressions return a valid value. 

Copy code 


alt(expr1[ , expr2 , expr3 , ...] , else)

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. The various elements you will need to enter are separated by commas
  3. Anything between square brackets [] is an optional parameter. In this function, [expr2] and [expr3] are optional parameters. Note how they are followed by ‘…’. This means that you can include more than 3 of these expressions if required. Note that when you include values for optional parameters in the function, they do not need to be enclosed in square brackets.

An example of this function being used with all the optional parameters included could be:

Copy code 


Alt("Jun Sales", "Jul Sales", "Aug Sales", 0)

The above function will return the sales figure from the first month in the list that is numeric. If all sales are not numeric, most likely blank or NULL, the last value will be returned – in this example, a 0.

An example of the function without using optional parameters may be:

Copy code 


Alt("Jun Sales", 0)

This function will return the June sales or 0 if there is no numeric value in the field.

Example 2: If

This function is probably the most common conditional function used as it is so easy to build a tree of conditional statements using it. It allows you to specify a condition or multiple conditions and what is to be done if they are true as well as if they are false.

Copy code 


if(condition , then [, else])

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. The various elements you will need to enter are separated by commas
  3. Anything between square brackets [] is an optional parameter. In this function, [else] is an optional parameter. Note that when you include values for optional parameters in the function, they do not need to be enclosed in square brackets.

An example of this function being used with all the optional parameters included could be:

Copy code 


if(Year("Date") = Year(Today()), 1 , 0)

This function creates a marker of 1 for all rows of data where the year of the field “Date” is the current year. You could then use this marker in set analysis for easy ‘current year’ expression calculations.

Without optional parameters. this function could be written as:

Copy code 


if(Year("Date") = Year(Today()), 1)

The alternative value is now NULL as we have not specified what to return if the condition evaluates to false. Depending on the need, this can be useful.

Note that this particular function can be used with multiple conditions such as:

Copy code 


if("Product Category" = 'Children' or "Product Parent Category" = 'Children' , 
'Kids' , 
"Product Category")

This code checks if the product’s category or parent category is ‘Children’; if it is, the category is renamed to ‘Kids’. The alternative is that the category remains unchanged. Note that you can also use ‘and’ in the condition and combinations of ‘or’ and ‘and’ to get a very granular condition.

How to use conditional 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 conditional functions in the load. The rules are:

  • They can be used to create new dimensions or calculated fields
  • They can be combined with other functions to create complex calculations
  • They can be used in the ‘where’ clause of the LOAD statement to filter the data

A very basic load may look something like this:

Copy code 


TABLE:
LOAD
   "Customer Name",
   "Total Sales",
   If("Total Sales" > 50000, 1, 0) as "Important Customer"

FROM [my_data.qvd] (qvd);

The loaded table will mark customers as 1 in the “Important Customer” field if their “Total Sales” are above £50,000. This marker can be used as a filter in the filter pane in the app or as a marker for set analysis.

How to use conditional functions in charts

Working with conditional functions in charts is very similar to working with them in the load. The rules are:

  • They can be used to create new dimensions or calculated fields.
  • They can be combined with other functions to create complex calculations.

Here is an example of using an If() function to remove a dimension from a chart by setting it to NULL. The formulas used are the axis labels but for clarity, the If() formula is:

Copy code 


If(Date >= '01/04/2023', Null(), Date)

Combining conditional functions with other functions

Conditional functions can be combined with other functions to get very granular conditions and results. Here we will look at two examples: nesting the functions and combining them with aggregation functions. You can combine conditional functions with other function types too.

Nesting conditional functions

Conditional functions can be nested with each other and functions can also be nested with itself.

Example 1: nested If()

The If() function can be nested with other If() functions to create granular conditions and results.

Copy code 


if(Year("Date") = Year(Today()), 1 , 
   if(Year("Date") = Year(AddYears(Today(),-1)), 2 , 
      if(Year("Date") = Year(AddYears(Today(),-2)), 3 , 
       0)))

This code will return a marker for the current year and two years prior, but any other year in the field “Date” will return a 0. This could be used in set analysis in the charts. Note that the ‘else’ part of the statement can also be functions, and they can be combined to create complex trees of logic.

Example 2: combining Pick() and Match()

Pick() is a function that returns the Nth expression in the list, with the N being specified first. Match() is a function that matches the specified value with a list of values and returns the match’s position. Combining the two functions gives a powerful but simple way to pick an expression given a specific set of matching criteria.

Copy code 


Pick(
   Match("Month",'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
   'Winter','Winter','Spring','Spring','Spring','Summer','Summer','Summer','Autumn','Autumn','Autumn','Winter')

This code will evaluate the value of the field “Month” and return the appropriate season (Winter/ Spring/ Summer/ Autumn).

Combining with aggregation functions

Combining conditional functions with aggregation functions is useful if you need to aggregate only specific values.

Example 1: combining Sum() and If()

When you need to sum only certain values within a field, you can specify which ones using an If() statement.

Copy code 


Sum(If("Month" = 'Jan', "Sales",0))

The simple code above will sum the “Sales” values only if the “Month” value is ‘Jan’, i.e. the resulting field is “January Sales”.

Example 2: combining Sum() and Coalesce()

The Coalesce() function returns the first non NULL value in the list of expressions. Combining these functions can be useful if your data to sum is between two separate fields, and you need to check both to decide on which value to sum.

Copy code 


Sum(Coalesce("Month 1 Sales", "Month 2 Sales", "Month 3 Sales"))

If aggregated by customer, this code will return the first month’s sales for each customer, regardless of which month they shopped in first.

Note that conditional functions can also wrap the aggregating function if required rather than being wrapped by the aggregator, as in these examples.