Tutorial - Chart Expressions

Qlik Sense®
May 2024
Copyright © 1993-2024 QlikTech International AB. All rights reserved.

HELP.QLIK.COM
© 2024 QlikTech International AB. All rights reserved. All company and/or product names may be
trade names, trademarks and/or registered trademarks of the respective owners with which they
are associated.
 Contents

1 Welcome to this tutorial! 4

1.1 What you will learn 4
1.2 Who should complete this tutorial 4
1.3 Lessons in this tutorial 4
1.4 Further reading and resources 4
2 Using expressions in visualizations 5
2.1 What is an expression? 5
2.2 Where can I use expressions? 5
2.3 When are expressions evaluated? 5
3 Which aggregation functions? 6
3.1 Consolidating amounts using Sum() 6
3.2 Calculating highest sale value using Max() 7
3.3 Calculating lowest sale value using Min() 8
3.4 Counting the number of entities using Count() 8
Difference between Count()and Count(distinct ) 9
4 Nested aggregations 11
4.1 Always one level of aggregation in a function 11
4.2 Using Aggr() for nested aggregations 11
4.3 Calculating largest average order value 12
5 Naked field references 15
5.1 Always use an aggregation function in your expression 15
Splitting invoice dates using the If() function 15
5.2 Avoiding naked field references 16
Avoiding naked field references in an If() function 16
6 The importance of Only() 19
6.1 Different expressions using Only() 21
7 Examples from real life 25
7.1 Calculating the gross margin percentage 25
7.2 Invoicing delays 27
7.3 Thank you! 31

Tutorial - Chart Expressions - Qlik Sense, May 2024 3

 1 Welcome to this tutorial!

1 Welcome to this tutorial!

This tutorial introduces chart expressions in Qlik Sense. Expressions are a combination
of functions, fields, and mathematical operators, used to process data and produce a
result that can be seen in a visualization.

Chart expressions are mostly used in measures. You can also build visualizations that are more
dynamic and powerful by using expressions for titles, subtitles, footnotes, and even dimensions.

1.1 What you will learn

When you have completed the tutorial, you will be comfortable using expressions in visualizations.

1.2 Who should complete this tutorial

You should be familiar with Qlik Sense basics. For example, you have loaded data, created apps,
and created visualizations on different sheets.

You will need access to the data load editor and should be allowed to load data in Qlik Sense
Enterprise on Windows.

1.3 Lessons in this tutorial

The topics in this tutorial could be completed in any order. However, later topics assume you are
familiar with previous topics. The screenshots were taken in Qlik Sense Enterprise SaaS. You may
see some visual differences if you are using Qlik Sense Enterprise on a different deployment.

1.4 Further reading and resources

l ≤ Qlik offers a wide variety of resources when you want to learn more.
l Qlik online help is available.
l Training, including free online courses, is available in the ≤ Qlik Continuous Classroom.
l Discussion forums, blogs, and more can be found in ≤ Qlik Community.

Tutorial - Chart Expressions - Qlik Sense, May 2024 4

 2 Using expressions in visualizations

2 Using expressions in visualizations

Visualizations in Qlik Sense are built from charts, which are built from dimensions and
measures. You can make your visualizations more dynamic and complex with
expressions.

Visualizations can have titles, subtitles, footnotes, and other elements to help convey information.
The elements that make up a visualization can be simple. For example: a dimension consisting of a
fieldrepresenting data, and a title consisting of text.

Measures are calculations based on fields. For example: Sum(Cost) means that all the values of the
field Cost are aggregated using the function Sum. In other words, Sum(Cost) is an expression.

2.1 What is an expression?

An expression is a combination of functions, fields, and mathematical operators (+ * / =).
Expressions are used to process data in an app in order to produce a result that can be seen in a
visualization. They can be simple, involving only basic calculations, or complex, involving functions
fields and operators. Expressions are used both in scripts and in chart visualizations.

All measures are expressions. The difference between measures and expressions is that
expressions have no name or descriptive data.

You can build visualizations that are more dynamic and powerful by using expressions for
dimensions, titles, subtitles, and footnotes. This means, for example, that instead of a static text,
the title of a visualization can be generated from an expression whose result changes depending on
your selections.

2.2 Where can I use expressions?

When you are editing a visualization, if an symbol can be seen in the properties panel, you can
use an expression. Click to open the expression editor, which is designed to help you build and
edit expressions. Expressions can also be entered directly into the expression field.

An expression cannot be saved directly as a master item. However, master measures and master
dimensions can contain expressions. If an expression is used in a measure or dimension which is
then saved as a master item, the expression in the measure or dimension is preserved.

2.3 When are expressions evaluated?

In a load script an expression is evaluated as the script executes. In visualizations, expressions are
evaluated automatically whenever any of the fields, variables or functions that the expression
contains change value or logical status. A few differences exist between script expressions and
chart expressions in terms of syntax and available functions.

Tutorial - Chart Expressions - Qlik Sense, May 2024 5

 3 Which aggregation functions?

3 Which aggregation functions?

Aggregation functions are many-to-one functions. They use the values from many
records as input and collapse these into one single value that summarizes all records.
Sum(), Count(), Avg(), Min(), and Only() are all aggregation functions.
In Qlik Sense, you need exactly one level of aggregation function in most formulas. This includes
chart expressions, text boxes, and labels. If you do not include an aggregation function in your
expression, Qlik Sense will automatically assign the Only() function.
l An aggregation function is a function that returns a single value describing some property of
several records in your data.
l All expressions, except calculated dimensions, are evaluated as aggregations.
l All field references in expressions must be wrapped in an aggregation function.

You can use the expression editor to create and change expressions in Qlik Sense.

3.1 Consolidating amounts using Sum()

Sum() calculates the total of the values given by the expression or field across the aggregated
data.

Let us calculate the total sales that each manager has made, as well at the total sales of all
managers.

Inside the app on the Which Aggregations? sheet you will find two tables, a table titled Sum(), Max
(), Min(), and a table titled Count(). We will use each table to create aggregation functions.

Do the following:

1. Select the available Sum(), Max(), Min() table.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following: Sum(Sales)
5. Click Apply.

Table showing total sales per Manager

Tutorial - Chart Expressions - Qlik Sense, May 2024 6

 3 Which aggregation functions?

You can see the sales that each manager has made, as well as the total sales of all managers.

As a best practice, make sure that your data is formatted appropriately. In this case, set
the Number formatting to Money, and the Format pattern to $ #,##0;-$ #,##0.

3.2 Calculating highest sale value using Max()

Max() finds the highest value per row in the aggregated data.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following : Max (Sales)
4. Click Apply.

Table showing total sales and highest sale per Manager

You can see that the highest sales earnings for each manager, as well as the highest total number.

Tutorial - Chart Expressions - Qlik Sense, May 2024 7

 3 Which aggregation functions?

3.3 Calculating lowest sale value using Min()

Min() finds the lowest value per row, in the aggregated data.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following : Min (Sales)
4. Click Apply.

Table showing total sales, highest sale, and lowest sale per Manager

You can see the lowest sales earnings for each manager, as well as the lowest total number.

3.4 Counting the number of entities using Count()

Count() is used to count the number of values, text and numeric, in each chart dimension.

In our data, each manager is responsible for a number of sales representatives (Sales Rep Name).
Let us calculate the number of sales representatives.

Do the following:

1. Select the available Count() table.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following : Count([Sales Rep Name])
5. Click Apply.

Table showing Sale Representatives, and total number of Sales Representatives.

Tutorial - Chart Expressions - Qlik Sense, May 2024 8

 3 Which aggregation functions?

You can see that the total number of sales representatives is 64.

Difference between Count()and Count(distinct )

Let us calculate the number of managers.

Do the following:

1. Add a new dimension to your table: Manager.

A single manager is handling more than one sales representative, so the same manager name
appears more than once in the table.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following: Count(Manager)
5. Add another measure with the expression: Count(distinct Manager)

6. Click Apply.

Table showing Sales Representatives, total number of Sales Representatives, Manager responsible for each
Sales Representative, incorrect total number of Managers, and correct total number of Managers.

Tutorial - Chart Expressions - Qlik Sense, May 2024 9

 3 Which aggregation functions?

You can see that the total number of managers on the column using Count(Manager) as an
expression was calculated as 64. That is not correct. The total number of managers is correctly
calculated as 18 using the Count(distinct Manager) expression. Each manager is only counted once,
regardless of how many times their name appears on the list.

Tutorial - Chart Expressions - Qlik Sense, May 2024 10

 4 Nested aggregations

4 Nested aggregations
Any field name in a chart expression must be enclosed by exactly one aggregation
function. If you need to nest aggregations, you can use Aggr() to add a second
aggregation level. Aggr() contains an aggregation function as an argument.

4.1 Always one level of aggregation in a function

A typical app may contain:
l one million records in the data
l one hundred rows in a pivot table
l a single KPI, in a gauge or text box

All three numbers may still represent all data, despite the difference in magnitude. The numbers are
just different aggregation levels.

Aggregation functions use the values from many records as input and collapse these into one single
value that can be seen as a summary of all records. There is one restriction: you cannot use an
aggregation function inside another aggregation function. You usually need every field reference to
be wrapped in exactly one aggregation function.

The following expressions will work:

l Sum(Sales)
l Sum(Sales)/Count(Order Number)

The following expression will not work because it is a nested aggregation:

l Count(Sum(Sales))

The solution to this comes in the form of the Aggr() function. Contrary to its name it is not an
aggregation function. It is a "many-to-many" function, like a matrix in mathematics. It converts a
table with N records to a table with M records. It returns an array of values. It could also be
regarded as a virtual straight table with one measure and one or several dimensions.

Use the Aggr() function in calculated dimensions if you want to create nested chart
aggregations on multiple levels.

4.2 Using Aggr() for nested aggregations

Aggr() returns an array of values for the expression, calculated over the stated dimension or
dimensions. For example, the maximum value of sales, per customer, per region. In advanced
aggregations, the Aggr() function is enclosed in another aggregation function, using the array of
results from the Aggr() function as input to the aggregation in which it is nested.

Tutorial - Chart Expressions - Qlik Sense, May 2024 11

 4 Nested aggregations

When it is used, the Aggr() statement produces a virtual table, with one expression grouped by one
or more dimensions. The result of this virtual table can then be aggregated further by an outer
aggregation function.

4.3 Calculating largest average order value

Let us use a simple Aggr() statement in a chart expression.

We want to see our overall metrics at the regional level, but also show two more complex
expressions:
l Largest average order value by manager within each region.
l Manager responsible for that largest average order value.

We can easily calculate the average order value for each region using a standard expression Sum
(Sales)/Count([Order Number]).

Inside the app, on the Nested Aggregations sheet you will find a table titled Aggr() function.

Do the following:

1. Select the available Aggr() function table.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following: Sum(Sales)/Count([Order Number])
5. Click Apply.

Table showing average order value per region.

As a best practice, make sure that your data is formatted appropriately. In this case, in
each column we will change the Label to represent the calculation. In columns with
monetary values we will change the Number formatting to Money, and the Format
pattern to $ #,##0;-$ #,##0.

Tutorial - Chart Expressions - Qlik Sense, May 2024 12

 4 Nested aggregations

Our goal is to retrieve the largest average order value for each region. We have to use Aggr() to tell
Qlik Sense that we want to grab the average order value for each region, per manager, and then
display the largest of those. To get the average order value for each region, per manager, we will
have to include these dimensions in our Aggr() statement:

Aggr(Sum(Sales)/Count([Order Number]), Region, Manager)

This expression causes Qlik Sense to produce a virtual table that looks like this:

Virtual table of Aggr() function showing average order value for each region, per manager.

When Qlik Sense calculates the individual average order values for each region, per manager, we
will need to find the largest of these values. We do this by wrapping the Aggr() function with Max():

Max(Aggr(Sum(Sales)/Count([Order Number]), Manager, Region))

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following : Max(Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
4. Click Apply.

Table showing region, average order value, and largest average order value for each region, per manager.

Tutorial - Chart Expressions - Qlik Sense, May 2024 13

 4 Nested aggregations

You can see the largest average order value for all managers at the region level. This is the first of
our two complex expressions! The next requirement is to have the name of the manager
responsible for these large average order values displayed next to the values themselves.

To do this, we will use the same Aggr() function as before, but this time together with the
FirstSortedValue() function. The FirstSortedValue() function tells Qlik Sense to provide us with
the manager, for the specific dimension specified in the second portion of the function:

FirstSortedValue(Manager,-Aggr(Sum(Sales)/Count(Order Number), Manager, Region))

There is one small, but very important, part of the expression: there is a minus symbol
before the Aggr() expression. Within a FirstSortedValue() function, you can specify the
sort order of the array of data. In this case, the minus symbol tells Qlik Sense to sort from
largest to smallest.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following: FirstSortedValue(Manager,-Aggr(Sum(Sales)/ Count([Order Number]),
Manager, Region))
4. Click Apply.

Table showing region, average order value, largest average order value for each region, and manager
responsible for that order value.

Tutorial - Chart Expressions - Qlik Sense, May 2024 14

 5 Naked field references

5 Naked field references

A field is considered naked when it is not enclosed in an aggregation function.
A naked field reference is an array, possibly containing several values. If so Qlik Sense will evaluate
it as NULL, not knowing which of these values you want.

5.1 Always use an aggregation function in your

expression
If you find that your expression does not evaluate correctly, there is a high chance that it does not
have an aggregation function.

A field reference in an expression is an array of values. For example:

Two tables, one showing that Max(Invoice Date) is a single value, and one showing that Invoice Date is an
array of values.

You must enclose the field Invoice Date in an aggregation function to make it collapse into a single
value.

If you do not use an aggregation function on your expression, Qlik Sense will use the Only()
function by default. If the field reference returns several values, Qlik Sense will interpret it as NULL.

Splitting invoice dates using the If() function

The If() function is often used for conditional aggregations. It returns a value depending on whether
the condition provided within the function evaluates as True or False.

Inside the app on the Naked field referencessheet you will find a table titled Using If() on Invoice
dates.

Do the following:

1. Select the available table titled Using If() on Invoice dates.

The properties panel opens.
2. Click Add column and select Measure.

Tutorial - Chart Expressions - Qlik Sense, May 2024 15

 5 Naked field references

3. Click on the symbol.

The expression editor opens.
4. Enter the following: If( [Invoice Date]>= Date(41323), 'After', 'Before' )
5. Click Apply.

Table showing invoice dates being split by a reference date.

This expression tests if the Invoice Date is before the reference date 2/18/2013 and returns 'Before'
if it is. If the date is after or equal to the reference date 2/18/2013, 'After' is returned. The reference
date is expressed as the integer number 41323.

5.2 Avoiding naked field references

At first glance, this expression looks correct:

If([Invoice Date]>= Date(41323) 'After', 'Before')

It should evaluate invoice dates after the reference date, return 'After' or else return 'Before'.
However, Invoice Date is a naked field reference, it does not have an aggregation function, and as
such is an array with several values and will evaluate to NULL. In the previous example, there was
only one Invoice Date per Date value in our table, so the expression calculated correctly.

Let's see how a similar expression calculates under a different dimensional value, and how to solve
the naked field reference issue:

Avoiding naked field references in an If() function

We will be using a similar expression as before:

If([Invoice Date]>= Date(41323), Sum(Sales))

This time the function sums the sales after the reference date.

Inside the app, on the Naked field references sheet you will find a table titled Sum(Amount).

Tutorial - Chart Expressions - Qlik Sense, May 2024 16

 5 Naked field references

Do the following:

1. Select the available Sum(Amount) table.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following: If( [Invoice Date]>= 41323, Sum(Sales) )
5. Click Apply.

Table showing year, sum of sales for each year, and the results of the expression using the If() function.

Keep the Label intact on the measures to show the differences between each
expression. In columns with monetary values, change the Number formatting to Money,
and the Format pattern to $ #,##0;-$ #,##0.

For each year there is an array of invoice dates that come after the reference date. Since our
expression lacks an aggregation function it evaluates to NULL. A correct expression should use an
aggregation function such as Min() or Max() in the first parameter of the If() function:

If(Max([Invoice Date])>= Date(41323), Sum(Sales))

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following: If( [Invoice Date]>= Date(41323), Sum(Sales) )
4. Click Apply.

Table showing year, sum of sales for each year, and the results of the different expressions using the If()
function.

Tutorial - Chart Expressions - Qlik Sense, May 2024 17

 5 Naked field references

Alternatively, the If() function can be put inside the Sum() function:

Sum(If([Invoice Date]>= Date(41323), Sales) )

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following: Sum( If([Invoice Date]>= Date(41323), Sales ) )
4. Click Apply.

Table showing year, sum of sales for each year, and the results of the different expressions using the If()
function.

In the second to last expression, the If() function was evaluated once per dimensional value. In the
last expression, it is evaluated once per row in the raw data. The difference in how the function is
evaluated causes the results to be different, but both return an answer. The first expression simply
evaluates to NULL. The picture above shows the difference between the expressions, using
2/18/2013 as the reference date.

Tutorial - Chart Expressions - Qlik Sense, May 2024 18

 6 The importance of Only()

6 The importance of Only()

Only() returns a value if there is only one possible value in the group. This value will be
the result of the aggregation. Qlik Sense defaults to Only() if no aggregation function is
specified.

If there is a one-to-one relationship between the chart dimensionand the parameter, the Only()
function returns the only possible value. If there are several values, it returns NULL. For example,
searching for the only product where the unit price =12 will return NULL if more than one product
has a unit price of 12.

The following images show the difference between one-to-one and one-to-many relationships:

A table showing one-to-one relationship between Manager Number and Manager

A table showing one-to-many relationship of Sales Rep Name and Manager.

Tutorial - Chart Expressions - Qlik Sense, May 2024 19

 6 The importance of Only()

The Only() function is an aggregation function. It uses many records as input and returns one value
only, similarly to Sum() or Count(). Qlik Sense uses aggregations in virtually all its calculations. The
expression in a chart, in a sort expression, in a text box, in an advanced search, and in a calculated
label are all aggregations and cannot be calculated without involving an aggregation function.

But what if a user enters an expression that lacks an explicit aggregation function? For example, if
the sort expression is set to Date? Or if there is an advanced search for customers who have
bought beer and wine products using the expression =[Product Type]='Beer and Wine'?

This is where the Only() function affects the calculation. If there is no explicit aggregation function
in the expression, Qlik Sense uses the Only() function implicitly. In the above cases, Only(Date) is
used as sort expression and Only([Product Type])='Beer and Wine' is used as the search
criterion.

Sometimes the new expression returns a result that the user does not expect. Both of the examples
above will work when there is only one possible value of Date or Product Type, but neither of them
will work for cases when there is more than one value.

Tutorial - Chart Expressions - Qlik Sense, May 2024 20

 6 The importance of Only()

6.1 Different expressions using Only()

We will create four KPIs with similar expressions. This way, we can compare how having naked field
references, or having Only() in a different position in our expression can have a big impact on your
selection results.

Inside the app on the Importance of Only()sheet you will find a filter pane with Invoice Date as the
dimension.

Do the following:

1. Create a KPI.
2. Click Add measure. Click on the symbol.
The expression editor opens.
3. Enter the following: Month([Invoice Date])
4. Create three more KPIs with measures: Month(Only([Invoice Date])), Month(Max([Invoice
Date])), and Only(Month([Invoice Date])).
5. Click Apply.

Four KPIs and a filter pane showing three different but similar expressions.

In each KPI the Number formatting has been set to Measure expression.

Tutorial - Chart Expressions - Qlik Sense, May 2024 21

 6 The importance of Only()

When you have a naked field reference, the Only() function is inserted at the lowest level. That
means that the first two KPIs, Month([Invoice Date]) and Month(Only([Invoice Date])), will be
interpreted the same and will always give the same result.

As you can see three of the four KPIs return NULL. The third KPI, Month(Max([Invoice Date])),
already returns a value, even though no selection has been made.

When you write expressions you should always ask yourself which aggregation you want to use, or
which value you want to use if there are several values. If you want to use NULL to represent
several values, you can leave the expression as is. For numbers, you probably want to use Sum(),
Avg(), Min(), or Max() instead. For strings you may want to use Only() or MinString().

Do the following:

1. Stop editing the sheet.

2. In the filter pane, select date in the month of January.
3. Confirm the selection by clicking .

The KPI results change when a single selection is made.

When a single selection is made, all of the KPIs return the correct answer. Even if the expression
contains a naked field reference, such as the expression in Month([Invoice Date]), the fact that we
have made a unique selection allows it to return the proper value.

Tutorial - Chart Expressions - Qlik Sense, May 2024 22

 6 The importance of Only()

Do the following:

1. In the filter pane, select an additional date in the month of January.

2. Confirm the selection by clicking .

The KPI results change when two selections are made with both dates in the month of January.

The first two KPIs return NULL, and the other two KPIs return the proper value of January.
Specifically, the fourth KPI returns a correct answer because both the date selections we made are
for dates in January.

Do the following:

1. In the filter pane, select an additional date, in a month other than January.
2. Confirm the selection by clicking .

The KPI results change when multiple selections are made with dates in different months.

Tutorial - Chart Expressions - Qlik Sense, May 2024 23

 6 The importance of Only()

When multiple selections are made, using dates in different months, only the third KPI returns a
value. It returns the value of the largest month from the selection made, according to the
expression Month(Max([Invoice Date])). Since Only() is inserted automatically in expressions with
naked field references you cannot always assume that the lowest level will be appropriate for your
expression. The placement of Only() is important.

Tutorial - Chart Expressions - Qlik Sense, May 2024 24

 7 Examples from real life

7 Examples from real life

Visualizations in Qlik Sense can give you insight in your data. Using expressions in your
charts can bring results that specifically apply to your work. The range of functions in
Qlik Sense allow you to customize your expressions to fit your needs, even if the option
is not readily available.

7.1 Calculating the gross margin percentage

We define the margin as the difference between our sales and the cost of making these sales. We
will calculate the margin for each month, as well as what percentage of the monthly sales is our
margin.

To calculate the margin percentage we can use the following expression:

(Sum(Sales)-Sum(Cost))/Sum(Sales)

The expression can be simplified further

1-Sum(Cost)/Sum(Sales)

Inside the app, on the Examples from real life sheet, you will find a table titled Margin.

Do the following:

1. Select the available table titled Margin.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following: Sum(Sales)
5. Add three more measures with the expressions: Sum(Cost), Sum(Sales) - Sum(Cost), and 1 -
Sum(Cost)/Sum(Sales).
6. Click Apply.

Table showing sum of sales, and sum of cost per month, as well as calculated margin per month in both
amount and percentage forms

Tutorial - Chart Expressions - Qlik Sense, May 2024 25

 7 Examples from real life

As a best practice, make sure that your data is formatted appropriately. In this case, in
each column we will change the Label to represent the calculation. In columns with
monetary values we will change the Number formatting to Money, and the Format
pattern to $ #,##0;-$ #,##0. Set the Number formatting of the margin percentage to
Number, and the Formatting to Simple and 12%.

You can see the calculated margin for each month based on the sales and the cost. You can also
see what percentage of the sales makes up our margin.

In the app data, we already have data for the monthly margin. This is a good opportunity to make a
comparison between our original data and our calculation.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.

Tutorial - Chart Expressions - Qlik Sense, May 2024 26

 7 Examples from real life

3. Enter the following : Sum(Margin)

4. Add another measure with the expression: (Sum(Sales) - Sum(Cost)) - Sum(Margin)
5. Click Apply.

The margin table with additional columns for monthly margin coming from the data set, and its difference to
the calculated margin.

Some values in the calculated margin column differ from the values from the margin column coming
directly from our data. The margin discrepancy column clearly shows that this takes place in a
months during 2014. The difference between the calculated margin and the margin coming from the
data set is small, but the fact that it takes place in a specific year creates some questions. What
changed during that year? Looking into the data and asking the right questions might prove to be
important for your business.

7.2 Invoicing delays

For this example we will be using data based on a company that collects dates both for the creation
of invoices and the promised delivery of the goods they produce. The two dates are not always the
same. Additionally some invoices might have two promised delivery dates. The shortest date is

Tutorial - Chart Expressions - Qlik Sense, May 2024 27

 7 Examples from real life

always the same as the invoice date, as it is automatically created by the invoicing system used by
the company. The largest promised delivery date is the date when a delivery was agreed to be
made between the company and the client.

Let us start by adding these dates on a table.

On the Examples from real life sheet you will find a table titled Invoicing delays.

Do the following:

1. Select the available table titled Invoicing delays.

The properties panel opens.
2. Click Add column and select Measure.
3. Click on the symbol.
The expression editor opens.
4. Enter the following : Only([Invoice Date])
5. Add another measure with the expression: Max([Promised Delivery Date])
6. Click Apply.

Table showing promised delivery date and invoice date for each invoice

As a best practice, make sure that your data is formatted appropriately. In columns that
show dates, set the Number formatting to Date, and set the Formatting to Simple and
17 Feb 2014.

You can see that the invoice date and the promised delivery date are not always the same. When
there are two promised delivery dates we need to use the largest one for our calculation.

Tutorial - Chart Expressions - Qlik Sense, May 2024 28

 7 Examples from real life

Let us calculate the difference between the invoice date and the promised delivery date. We will
use the following expression:
Max([Promised Delivery Date])-[Invoice Date]

There are three scenarios:

l The two dates are the same, and the result of the expression is 0.
l The products were promised after the invoice was created, and the result is a positive
integer.
l The invoice was created after the products were promised to be delivered, and the result is a
negative integer.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following : Max([Promised Delivery Date])-[Invoice Date]
4. Click Apply.

Table showing promised delivery date and invoice date for each invoice, as well as the number of days from
invoicing to promised delivery

Sort the table based on the last column, named Days from invoicing to delivery.

Tutorial - Chart Expressions - Qlik Sense, May 2024 29

 7 Examples from real life

There is a range of differences between the dates. Negative values indicate that the invoice was
delayed. Positive numbers indicate that the promised delivery was done after the invoice was
created.

Let us calculate the number of invoices that were made after the promised delivery date.

Do the following:

1. Click Add column and select Measure.

2. Click on the symbol.
The expression editor opens.
3. Enter the following: Count(Distinct If(Aggr(Max([Promised Delivery Date])<[Invoice Date],
[Invoice Number]),[Invoice Number]))
4. Click Apply.

Alternatively we could use Sum(Aggr(If(Max([Promised Delivery Date])-[Invoice Date]<

0, 1, 0), [Invoice Number])).

The invoicing delays table with additional column showing the number of delayed invoices.

The last column makes more sense as a KPI as a percentage of the total number of invoices.

Do the following:

1. Create a KPI.
2. Click Add measure. Click on the symbol.
The expression editor opens.
3. Enter the following: Count(Distinct If(Aggr(Max([Promised Delivery Date])<[Invoice Date],
[Invoice Number]),[Invoice Number]))/Count([Invoice Number])
4. Click Apply.

Tutorial - Chart Expressions - Qlik Sense, May 2024 30

 7 Examples from real life

A KPI showing the percentage of delayed invoices.

Let us calculate the average delay in invoicing.

Do the following:

1. Create a new KPI.

2. Click Add measure. Click on the symbol.
The expression editor opens.
3. Enter the following: Avg(Aggr(If(Max([Promised Delivery Date])<[Invoice Date],(Max
([Promised Delivery Date])-[Invoice Date])), [Invoice Number]))
4. Click Apply.

A KPI showing the average delay in invoicing

7.3 Thank you!

Now you have finished this tutorial, and hopefully you have gained some basic knowledge about
chart expressions in Qlik Sense. Please visit our website for more inspiration for your apps.

Tutorial - Chart Expressions - Qlik Sense, May 2024 31