Advanced Set Analysis and

Advanced Aggregation
Hands-On Exercises
Authorized translation excerpted from the English language edition QlikView Your Business:
An Expert Guide to Business Discovery with QlikView and Qlik Sense.
Copyright 2015 by John Wiley & Sons, Inc.
All Rights Reserved. This translation published under license with the original publisher John
Wiley & Sons, Inc.
Exercise 1: Bucket Analysis
1. Open the sheet named 1. Buckets.
2. Create a Bar Chart.
3. Create a Calculated Dimension and add the formula that calculates the Bucket based on
the Profit Margin, for each Customer (explained below).
4. Use the following formula for the calculation of the Profit Margin:

(sum([# Amount]) - sum([# COGS]) - sum([# CM Amount])) / sum([# Amount])

5. Add the following transformations to the formula:

5.1. Rank all Customers based on performance, using Rank()

5.2. Divide the Rank by the total number of Customers
5.3. Multiply the percentile by the desired number of buckets (vBuckets) and round up
using Ceil().
5.4. In order to use in a Dimension, enclose in AGGR() by Customer

6. Your complete formula should look like this:

=AGGR(
ceil(
rank(
(sum([# Amount]) -
sum([# COGS]) -
sum([# CM Amount])) / sum([# Amount])
, 4)
/ count(DISTINCT TOTAL Customer) * vBuckets)
, Customer)

7. Add the Expression that calculates Average Sales by Customer:

sum([# Amount])/count(DISTINCT Customer)
8. Sort the chart by Bucket, in the ascending numeric order.

Exercise 2: Counting Colors

1. Open the sheet named 2. Colors.
2. Create a detailed Straight Table chart on top of the sheet:
1.1. Use Product as the Dimension.
1.2. Add the Expression for Sales:
sum([# Amount])
1.3. Add the Expression for Gross Margin %:
(sum([# Amount]) - sum([# COGS])) / sum([# Amount])
1.4. Using Text Color expression attribute, color Gross Margin % based on the values
25% and higher should be colored green, negative margins should be colored red, and
all values between 0 and 25% should be colored black. For example:
IF( (sum([# Amount]) - sum([# COGS])) / sum([# Amount]) >0.25, Green(), 0)
3. Create a summarized Straight Table chart below, to show counts of Green, Black and Red
colors by Brand:
3.1. Use Brand as a Dimension.
3.2. Use the following formula in the Expression for the Green lights:

sum(
AGGR(
IF( (sum([# Amount]) - sum([# COGS])) / sum([# Amount]) >0.25, 1, 0)
, Product)
)

3.3. Use similar formulas (adjust the conditions conditions) for Black and Red colors.
4. Clone the summarized chart and change the Dimension to Channel.
4.1. The calculation by Channel requires adding the Channel field to the list of AGGR
dimensions:

sum(
AGGR(
IF( (sum([# Amount]) - sum([# COGS])) / sum([# Amount]) >0.25, 1, 0)
, Product, Channel)
)

4.2. Can you explain why?

Exercise 3: Advanced Lookup

1. Open the sheet named 3. Lookup.
2. Create a detailed Straight Table chart. Use Salesperson as a Dimension.
3. Create 3 Expressions in the provided Straight Table, to show the following calculations for
each SalesPerson:
The highest Monthly Sales amount
The Year/Month that was the best for Sales
The most profitable Customer for each Salesperson
4. For Best Monthly Sales, use nested aggregation with AGGR() by Salesperson and
MonthYear. Within the AGGR() function, summarize Sales amounts. Outside of AGGR(),
calculate Max() of these pre-aggregated values:

max(
AGGR(
sum([# Amount]),
Salesperson, MonthYear)
)

3. For Best Sales Month, use FirstSortedValue() for the field MonthYear, sorted by the same
AGGR() function of aggregated Sales by Salesperson and MonthYear. Multiply by -1 to sort
in the descending order:

FirstSortedValue( MonthYear,
-AGGR(sum([# Amount]), Salesperson, MonthYear)
)
4. For Most Profitable Customer, build a similar FirstSortedValue() expression for the field
Customer, sorted by the AGGR() function of aggregated Profit Margins by Salesperson and
Customer:
FirstSortedValue( Customer,
-AGGR((sum([# Amount]) - sum([# COGS]) - sum([# CM Amount]))
/ sum([# Amount])
, Salesperson, Customer)
)

Exercise 4: Top Performers

1. Open the sheet named 4. Top Performers.
2. Create a Pivot Table to show the best performing Styles per Brand, based on Sales, along
with their Sales amounts:
2.1. Add Brand to the list of Dimensions.
2.2. Add a Calculated Dimension that will calculate three top Styles per Brand:
=AGGR(
IF(rank(sum([# Amount]), 4) <=3, Style)
, Brand, Style
)
2.3. Suppress Null values for both Dimensions.
2.4. Add the usual Sales expression:
sum([# Amount])
3. Examine the result.

Exercise 5: Aggregated Filter

1. Open the sheet named 5. Aggregated Filter.
2. Create a List Box (QlikView), or a Filter Pane (Qlik Sense).
3. In QlikView, scroll down to the bottom of the list of Fields and select Expression. In Qlik
Sense, add a Dimension and click on the f(x) button to open the expression editor.
4. Enter the following aggregated formula that calculates the Ratio of Credit Memos to Sales:
=AGGR(
num(ceil(sum([# CM Amount]) / sum([# Amount]), 0.1), '#,##0%')
, Customer
)
5. Name the List Box / Label the calculated dimension as Ratio of Credit Memos to Sales.
6. Make a selection and notice what field is getting selected.
Exercise 6: Limiting Dimension Values
1. Open the sheet named 6. Limits.
2. Create a Scatter chart .
3. Use Product as the Dimension
4. Add two Expressions for the X and Y coordinates:
4.1. Sales Change in %:
sum({<_CYTD_Flag={1}>} [# Amount]) /
sum({<_PYTD_Flag={1}>} [# Amount]) - 1
4.2. Change in Margin Points:
(sum({<_CYTD_Flag={1}>} [# Amount])
sum({<_CYTD_Flag={1}>} [# COGS])) /
sum({<_CYTD_Flag={1}>} [# Amount])
-
(sum({<_PYTD_Flag={1}>} [# Amount])
sum({<_PYTD_Flag={1}>} [# COGS])) /
sum({<_PYTD_Flag={1}>} [# Amount])

5. Format the numbers as percentages and examine the result.

6. In order to limit the chart to the top 20 Products, add the following condition to all Set
Analysis conditions, in all aggregation functions:
Product = {"=rank(sum([# Amount]),4)<=20"}
7. Extra credit: clone the same chart and limit the second chart by top 20 Spring products.
7.1. Add the filter for Spring to the SUM() function within the ranking function:
Product = {"=rank(sum({<Season={Spring}>} [# Amount]),4)<=20"}
Exercise 7: Restoring Missing Links
1. Open the sheet named 7. Links.
2. Create two List Boxes (in QlikView) or a Filter Pane (in Qlik Sense) for the fields [Reason
Category] and [Reason Description].
3. Create a Straight Table the shows Credit Memo amounts and Sales amounts by Customer:
3.1. Use Customer as the Dimension
3.2. Add an expression for Credit Memos:
sum([# CM Amount])
3.3. Add an expression for Sales Associated with Credit Memos:
Sum([# Amount])
4. Notice that selecting Reasons or Reason Categories breaks the Sales Expression.
5. Add the Set Analysis condition to the Sales expression, in order to restore the missing
links:
sum({< Customer=P({<FactType={'CM'}>}) ,
[Reason Category]=,
[Reason Description]=,
[Reason Code]=
>} [# Amount])
6. Verify that selecting Reasons doesnt break the calculation of Sales.

Exercise 8: Aggr() and Synthetic Dimensions

1. Open the sheet named 8. Synthetic Dimensions.
2. Create a Bar Chart that shows the counts of Profitable and Not Profitable customers:
2.1. Create a Calculated Dimension and name it Customer Profitability:
=valuelist('Profitable', 'Not Profitable')
2.2. Create an Expression that counts profitable and not profitable Customers, conditioned
based on the Synthetic Dimension value:
pick(match(valuelist('Profitable', 'Not Profitable'), 'Profitable', 'Not Profitable') ,
count( distinct AGGR(if((sum([# Amount]) - sum([# COGS]) - sum([# CM Amount]))
/ sum([# Amount]) >0 , Customer) , Customer)) ,
count( distinct AGGR(if((sum([# Amount]) - sum([# COGS]) - sum([# CM Amount]))
/ sum([# Amount]) <=0 , Customer) , Customer)) ,
)
2.3. Notice that the chart only shows the first value but not the second value.
3. Create a similar Bar Chart that solves the problem using the NODISTINCT prefix in AGGR:
3.1. Clone the existing chart and add the keyword NODISTINCT to all AGGR functions.
3.2. Verify that both bars are displayed in the chart.
4. Create a similar Bar Chart that solves the problem by calculating the Dimension value
using AGGR(), instead of using ValueList():
4.1. Clone the first bar chart
4.2. Replace the formula in the Calculated Dimension with the following AGGR()
calculation:
=AGGR(
if((sum([# Amount]) - sum([# COGS]) - sum([# CM Amount])) /
sum([# Amount]) >0 , 'Profitable', 'Not Profitable')
, Customer)
4.3. Replace the formula in the Expression by the distinct count of Customers:
count(distinct Customer)
4.4. Verify that both bars are displayed in the chart.
5. Create a similar Bar Chart that solves the problem using Advanced Search in Set Analysis:
5.1. Clone the first bar chart.
5.2. Keep the same Synthetic Dimension
5.3. Modify the Expression to replace the AGGR() function with a Set Analysis expression
that uses Advanced Search with a similar condition:
pick(match(valuelist('Profitable', 'Not Profitable'), 'Profitable', 'Not Profitable') ,
count(distinct
{<Customer={"=(sum([# Amount]) - sum([# COGS]) - sum([# CM Amount])) /
sum([# Amount])>0"}>} Customer)
,
count(distinct
{<Customer={"=(sum([# Amount]) - sum([# COGS]) - sum([# CM Amount])) /
sum([# Amount])<=0"}>} Customer)
)
5.4. Verify that both bars are displayed in the chart.

Exercise 9-1: Point in Time Comparison

1. Open the sheet named 9. YTD.
2. Create two List Boxes (QlikView) or a Filter Pane (Qlik Sense) for the fields Year and
Month.
3. Create a Straight Table that shows Average Ratio of Credit Memos to Sales across
Customers by Brand for Current YTD, compared to the Prior YTD:
3.1. Use Brand as the Dimension
3.2. Add an expression for Current YTD Average Credit Memos to Sales:
Avg(
AGGR(
sum({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>}
[# CM Amount])/
sum({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>} [# Amount])
, Customer, Brand)
)
3.3. Clone this expression and replace CYTD flag with the PYTD flag for Prior YTD.
4. Make selections in the field Year and Month and notice that the Expression values are
changing. Can you tell why?

Exercise 9-2: Improving the Point in Time Comparison

1. Clone the Straight table.

2. In both chart expressions, copy and paste the same Set Analysis expression from the inner
aggregation functions sum() into the outer aggregation function avg():
Avg({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>}
AGGR(
sum({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>}
[# CM Amount])/
sum({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>} [# Amount])
, Customer, Brand)
)
3. Verify that Year and Month selections dont impact the calculation anymore.

Exercise 9-3: Placing the Set in the AGGR

1. Clone the second chart that you created in Exercise 9-2.
2. In both chart expressions, replace the repeated Set Analysis conditions in the inner
aggregation functions with a single Set Analysis condition within the AGGR() function:
Avg({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>}
AGGR({$<_CYTD_Flag={1}, Year=, Month=, Quarter=>}
sum([# CM Amount])/sum([# Amount])
, Customer, Brand)
)
3. Verify that the chart produces the same results as the previous chart, only with a more
elegant expression.

Exercise 10-1: Using the Above() Function

1. Open the sheet named 10. Above.
2. Create two List Boxes (QlikView) or a Filter Pane (Qlik Sense) for the fields Year and
Month.
3. Create a Straight Table that shows Sales and Prior Month Sales by Sales Person and
Month/Year:
3.1. Use Salesperson and MonthYear as the Dimensions.
3.2. Add an Expression for Sales:
sum([# Amount])
3.3. Add an Expression for Prior Month Sales:
above(sum([# Amount]))
3.4. Ensure that the chart is sorted by Salesperson and then by MonthYear.
4. Create a Combo Chart that shows Monthly Sales along with a 12-Month Average line:
4.1. Use MonthYear as a Dimension
4.2. Add the expression for Sales and configure it as Bars.
4.3. Add the expression for 12 Months Average and configure it as a Line:
rangeavg(above(sum([# Amount]), 0, 12))

Exercise 10-2: Enhancing the Use of Above()

1. In the Straight Table, create a new Expression that calculates the Prior Month Sales using a
sorted AGGR():
sum(
aggr(
above(sum([# Amount]))
, Salesperson
, (MonthYear, (NUMERIC, ASCENDING))
)
)
2. In the Combo Chart, add another average line that shows the True Average thats not
dependent on Calendar selections:

aggr( {<Month=, Year=, MonthYear=>}

rangeavg(above(sum([# Amount]), 0, 12))
, (MonthYear, (NUMERIC, ASCENDING))
)

Exercise 11-1: Pareto Calculation

1. Open the sheet named 11-1.Pareto.
2. Create a List Box or a Filter Pane for the field Customer.
3. Create a Text Object to show the total number of customers that generate 80% of Sales.
The desired result should be 35 out of 199. Use the following formulas as the basis. In
QlikView, concatenate both numbers and the words out of into one string. In Qlik
Sense, use separate Measures and the same words as a separate text.
3.1. Pareto Customers:
count({$<Customer=>}
AGGR({$<Customer=>}
if(rangesum(Above(sum([# Amount])/sum( total [# Amount]) , 0,
RowNo())) <= 0.8, Customer)
, (Customer, (=sum([# Amount]), DESC))
)
)
3.2. All Customers:
count ({$<Customer=>} Customer)
4. Clone the same Text Object and replace the Pareto Customers calculation with the Set
Analysis formula:
count(
{<Customer = {"= AGGR({$<Customer=>}
rangesum(Above(sum([# Amount])/sum(total [# Amount]) , 0, RowNo())) ,
(Customer, (=sum({$<Customer=>} [# Amount]), DESC))) <0.8"}>}
Customer)

5. Create a Straight Table that shows the Number of Pareto Customers by Channel:
5.1. Use Channel as a Dimension
5.2. Create an Expression for the Number of Pareto Customers:
count({$<Customer=>} distinct
AGGR({$<Customer=>}
if(rangesum(Above(sum([# Amount])/sum( total <Channel> [# Amount]) , 0,
RowNo())) <= 0.8, Customer)
, Channel
, (Customer, (=sum([# Amount]), DESC))
)
)

& ' out of ' &

count ({$<Customer=>} Customer)

6. Clone the Straight Table and transform it to show the Number of Pareto Customers by
Brand:
6.1. Use Brand as a Dimension
6.2. Replace Channel in the list of AGGR dimensions by Brand:
count({$<Customer=>} distinct
AGGR({$<Customer=>}
if(rangesum(Above(sum( [# Amount])/sum(total <Brand> [# Amount]) , 0,
RowNo())) <= 0.8, Customer)
, Brand
, (Customer, (=sum( [# Amount]), DESC))
)
)

& ' out of ' &

count ({$<Customer=>} Customer)

Exercise 11-2: Enhancing the Pareto calculation

1. Clone the sheet that was developed in the previous exercise and name the new sheet 11-2.
Pareto #2
2. In the first Text object, enclose the field Customer in the function only() and add the same
Set Analysis condition into the sorting expression of the AGGR:
count({$<Customer=>}
AGGR({$<Customer=>}
if(rangesum(Above(sum([# Amount])/sum( total [# Amount]) , 0, RowNo()))
<= 0.8, only(Customer))
, (Customer, (=sum({$<Customer=>} [# Amount]), DESC))
)
)
3. In the second Text Object, wrap the AGGR() function in the only() function with the same
Set Analysis condition. Add the same condition to the sorting function within the AGGR:
count(
{<Customer = {"=only({$<Customer=>} AGGR({$<Customer=>}
rangesum(Above(sum([# Amount])/sum(total [# Amount]) , 0, RowNo()))
, (Customer, (=sum({$<Customer=>} [# Amount]), DESC)))) <0.8"}>}
Customer)
4. In both Straight Tables, apply the same changes as in the first Text Object enclose the
field Customer in only() and add the Set Analysis condition to the sorting function:
count({$<Customer=>} distinct
AGGR({$<Customer=>}
if(rangesum(Above(sum([# Amount])/sum(total <Channel> [# Amount]) ,
0, RowNo())) <= 0.8, only(Customer))
, Channel
, (Customer, (=sum({$<Customer=>} [# Amount]), DESC))
)
)
& ' out of ' &
count ({$<Customer=>} Customer)