Learning module: Advanced Set Expressions

Exercise - INSTRUCTIONS
Using the Example Download instructions on the right, open the Advanced Example Download
Set Expressions Exercise.qvf file. This configured app will be used to provide The example file supporting this exercise is
an opportunity for you to practice applying set expressions in Qlik Sense. available in the ‘Example files’ section of
this learning module. Access the zip
archive, which contains all of the example
files for this module, and extract its
contents.
Set expressions with comparison operators Advanced Set Expressions Exercise.qvf
• Open the sheet titled: Shipping Analysis. can be opened in one of the following Qlik
Sense deployments:
• The table on this sheet displays the individual shipping costs for each
order. And, the KPI chart displays the total count of orders.
Qlik Sense SaaS (Business, Enterprise
• Our goal is to create additional KPI charts which reflect counts of
SaaS) instructions:
orders that meet specific ranges of shipping costs per order.
• Use the Add new button to Upload
o Change the app to Edit sheet mode, and toggle the app.
Advanced options switch ON (if applicable). • Drag & drop the example QVF file
o Copy the existing KPI chart, and Paste the copy immediately onto the resulting drop target.
below that chart. • Upload to your Personal space.
o For the copied KPI, use the Data section of the properties • Click to open uploaded app.
panel to edit the expression in order to calculate the count of
orders which have shipping costs which are greater than
79.00.

▪ The resulting expression should appear as you see Qlik Sense Desktop instructions:
here: • Drag & drop the example QVF file
=Count({1<ShippingCost={">79.00"}>} DISTINCT onto the Qlik Sense Desktop hub
OrderID)
• Edit the Label to read: “Count of Orders with
Shipping Costs Greater than 79.00”
Qlik Sense Enterprise on Windows
o Click the button to exit edit mode. instructions:

• May require the assistance of your

• Click on the ShippingCost column name in order to sort the table from Qlik Sense Administrator to Import
highest to lowest. Note that there are only 10 orders with shipping costs the app into your Personal > Work
that are greater than 79.00. space, using the Qlik Management
Console.

© 2023 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.

Exercise instructions | page 1

Set expressions with comparison operators (cont’d)
• The results should appear as you see below:

• Our next goal is to create an additional KPI chart which reflects the count of orders that range between
45.00 and 45.75.

o Toggle the app into Edit sheet mode.

o Copy/paste the most recent KPI chart, and edit the expression to achieve a count of orders which
range between 45.00 and 45.75.
▪ The resulting expression should appear as you see here:

=Count({1<ShippingCost={">45.00<45.75"}>} DISTINCT OrderID)

▪ Edit the Label to read: “Count of Orders with Shipping Costs Between 45.00 and
45.75”.

o Click the button to exit edit mode.

• The resulting KPI chart should appear as you see here:

• Ensure that the table visualization is sorted by ShippingCost values, then scroll down and examine the
orders which range between 45.00 and 45.75. The count of these orders should be 11.

© 2023 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.

Exercise instructions | page 2

Set expressions with operators
• Proceed to the next sheet in the app, Office Analysis.

• This sheet contains a table which lists the JobTitle and SalesOffice where each employee works.
• Use the Filter panes provided to isolate each of the following selected sets of data, independently, and
note the Employee Count value displayed in the KPI chart.
o SalesOffice = Lund (Employee Count = 11) – clear the selection
o JobTitle = Systems Manager (Employee Count = 6)
o SalesOffice = Lund -and- JobTitle = Systems Manager (Employee Count = 4)

• Clear all selections.

• Our goal is to create a KPI chart which calculates the employee count for the Systems Managers who are
not located in the Lund office.
o Toggle the app into Edit sheet mode.
o Copy the existing KPI chart, and paste the copy immediately below that chart.
o For the copied KPI, use the Data section of the properties panel to edit the expression.
▪ The resulting expression should define two sets, one which modifies a set to isolate the
Systems Manager value in the JobTitle field, and the other which modifies a set to isolate
the Lund value in the SalesOffice field.
▪ Apply an operator between the two sets which excludes the Lund-containing values from
the Systems Manager set.
▪ The resulting expression should appear as you see below:
=Count({1<JobTitle={'Systems Manager'}>-1<SalesOffice={Lund}>} DISTINCT EmployeeName)

• Edit the Label to read: “Systems Managers not in Lund”.

o What is the resulting count of Systems Managers not in Lund? (Answer = 2)

o Click the button to return the sheet to ‘analyze’ mode. If you use the Filter pane to select Systems
Managers again, the Table visualization shows that the two employees who are not in the Lund
sales office are both in the Seattle office.

• Clear all selections.

© 2023 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.

Exercise instructions | page 3

Set expressions with operators (cont’d)

• What expression operator would you use if you wanted to create a set expression which counts
the employees who are Systems Managers or are in the Lund sales office, or both? (Answer = the
(+) or UNION operator.)
o Copy/paste the most recent KPI chart and change the operator to achieve this count.
▪ Edit the Label to read: “Systems Managers UNION Lund”.

• What expression operator would you use if you wanted to create a set expression which counts
the employees who are Systems Managers or are in the Lund sales office, but are not both?
(Answer = the (/) or XOR operator.)
o Copy/paste the most recent KPI chart and change the operator to achieve this count.
▪ Edit the Label to read: “Systems Managers XOR Lund”.

© 2023 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.

Exercise instructions | page 4

Set expressions with operators (cont’d)
• The resulting KPI values on this final sheet should appear as you see below:

=Count(DISTINCT EmployeeName)

=Count({1<JobTitle={'Systems Manager'}>-1<SalesOffice={Lund}>} DISTINCT EmployeeName)

=Count({1<JobTitle={'Systems Manager'}>+1<SalesOffice={Lund}>} DISTINCT EmployeeName)

=Count({1<JobTitle={'Systems Manager'}>/1<SalesOffice={Lund}>} DISTINCT EmployeeName)

© 2023 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.

Exercise instructions | page 5

Apply an alternate state name as a set expression
• Proceed to the sheet, Sales Analysis, and ensure that the app is in Edit sheet mode.

• Currently, we have two visualizations on this sheet which use the ‘normal’ selection state. We intend to
create an alternate selection state which will allow us to compare different product category selections in
the same bar chart.

o Open the Master items section of the assets panel, and use the Alternate states subsection to
create a new selection state.
▪ Name the state: “OtherSelection” (with no spaces in the name).

o Copy/paste in order to duplicate the filter pane on this sheet, and visit the Appearance section of
the properties panel for this new filter pane.
▪ Expand the Alternate states subsection, and apply the state named: OtherSelection.

▪ In the Data section of the properties panel, expand the CategoryName dimension, and
change the Title to read: “OtherCategoryName”.
o Activate the bar chart, and use the Add button beneath the Measures area in the Data section of
the properties panel to add a new measure to this chart.

▪ Construct a set expression which will calculate the sum of Sales values for the data
selected in the OtherSelection state.

▪ The resulting expression should appear as you see below:

=Sum({"OtherSelection"} Sales)

• Use the button to exit edit mode.

• Test the set expression configured on this sheet by selecting different CategoryName values in each filter
pane. For example, compare the sales in different regions for Bath Clothes vs. Children’s wear.
• The results should appear as you see here:

© 2023 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.

Exercise instructions | page 6

Set expressions with the element function P
• Clear all selections, proceed to the sheet, Customer Analysis, and change to Edit sheet mode.

• The current KPI chart displays the total Sales amount to all customers. Our goal is to calculate the sales
to only those customers who purchased the product: Aino Shoes.
o Copy/paste the current KPI chart, and edit the measure expression to calculate the amount of
Sales for only those customers who bought Aino Shoes.

o The result should be the following expression construct:

= Sum({1<Customer=(P({1<ProductName={'Aino Shoes'}>}))>}Sales)
▪ Edit the Label to read: “Total sales to customers who purchased Aino Shoes”.

Applying a wildcard character to search for a substring in a set expression

• Now, we wish to create another KPI which further limits the sales total to those customers who purchased
Aino Shoes or products starting with the letter ‘G’.

o Copy/paste the most recent KPI chart, and adjust the measure expression to calculate the
combined sales of all customers who purchased Aino Shoes or products starting with the letter
‘G’.

o The result should be the following expression construct:

= Sum({1<Customer=(P({1<ProductName={'Aino Shoes',"G*"}>}))>}Sales)

▪ Edit the Label to read: “Total sales to customers who purchased Aino Shoes or
products starting with the letter ‘G’”.

© 2023 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.

Exercise instructions | page 7