Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

A Primer on Set Analysis

Set analysis is one of the more complex things you can use in QlikView or Qlik Sense. Its syntax is often perceived as complicated and there are some misunderstandings around it. So here is my short explanation.

Set analysis is a way to define an aggregation scope different from current selection. Think of it as a way to define a conditional aggregation. The condition, however, is in itself like a selection that is evaluated before the cube (the chart) is expanded. Hence, it is not possible to have a condition that is evaluated row by row.

 

The set analysis is used inside an aggregation function, e.g. in an expression like

 

          Sum(Sales)

 

The first step is to add the markers for the set analysis – the curly brackets:

 

          Sum( {…} Sales)

 

These define the record set over which the aggregation should be made. Inside, you can use different identifiers and operators, e.g. ‘$’ for records implied by current selection, ‘1’ for all records, ‘1-$’ for all excluded records, etc.

 

A set of records that you can define by a simple selection is called natural set. Not all record sets are natural; for instance, {1-$} cannot always be defined through a selection.

 

The next step is often to add a set modifier, which is defined by angle brackets. The set modifier adds or changes a selection. It can be used on any natural set and consists of a list of fields, where each field can have a new selection:

 

          Sum( {$<…>} Sales)

 

The next step is to define the element set for a field; the set of field values that defines the selection. The element set could be a field reference or a set function, P() or E(). It is more common, though, that it is an explicit list of field values or a search, and then you need the curly brackets to define the element set:

 

          Sum( {$<Date={…}>} Sales)   or   Sum( {$<Date=P(…)>} Sales)

 

A search can be defined through double quotes. This way, field values that match the search string will be selected:

 

          Sum( {$<Date={"…"}>} Sales)

 

Do not use single quotes to initiate a search here! Single quotes should denote literals, i.e. explicit field values. (Yes, today single quotes work as a search, but this is a bug that one day will be fixed…)

 

Often a numeric search is made, defined by a leading relational operator. Then, field values will be selected based on a comparison:

 

          Sum( {$<Date={"<=…"}>} Sales)

 

To make it worse, the value to which the field values are compared is often a calculated one. And in order to get a calculated value into the expression, a dollar expansion is needed.:

 

          Sum( {$<Date={"<=$(…)"}>} Sales)

 

Inside the dollar expansion, you need an expression that starts with an equals sign and contains an aggregation function, e.g.:

 

          Sum( {$<Date={"<=$(=Max(Date))"}>} Sales)

 

This aggregation function is evaluated globally, before the cube is expanded.

 

As you can see, there are many levels of a set expression, and many pairs of brackets and delimiters that need to match. When you write set expressions, you should always write both brackets directly, and then continue with the expression between them. This way you will avoid simple syntax errors.

 

Good luck with your set analysis expressions!

 

HIC

 

Further reading related to this topic:

Why is it called Set Analysis?

Dates in Set Analysis

Excluding values in Set Analysis

Introduction to Set Analysis (video) - Part 1

35 Comments
Partner
Partner

Problem is you want the set analysis applied for each row but set analysis does not work that way.
It is applied once for the expression before it is evaluated for each dimension value.
Thus the set analysis does not know anything about your dimension values.
In set analysis you do a filtering of data in code just as the end user does with filter boxes.
To have the set analysis reevaluated for each dimension value would be costly in CPU and RAM.

0 Likes
136 Views
robert99
Valued Contributor II

H Anders

The issue is I all been unable to calculate the correct figures for a period chart. All I would like is the option to link to the dimension in the situation like the one above knowing that it's costly In CPU and RAM. I've been working on this off and on for about a week now. I still don't know how to do it. Maybe its possible without using AsOfMonth (using Peek accumulating instead) but I also want to do it with OD Debt 30-60 days etc. This is also possible for one period but not for the all period at once

0 Likes
136 Views
Partner
Partner

HI Robert,

I don't fully understand your use case - I'll make an educated guess that you're trying to exclude certain accounts from contributing towards your totals if the net position per month is = zero. There must be a bit more to it though as if the net position per month per account is zero - it won't impact the totals, so why take it out? Anyway, disregarding my requirement knowledge gap - to support this type of logic you'll need to make use of the Aggr statement. It allows you to compute figures at a grain of your choosing, so you can do your sum(Value)>0 check per account per month and sum up the remainder. Something like the following should do the trick:

sum(aggr(if(Sum(ARValue)>0,sum(ARValue)),CustAccNum,AsOfMonth))


... or trying to second-guess your actual business logic requirement, minor tweak to ignore any selections in your net zero check:


sum(aggr(if(Sum({1} ARValue)>0,sum(ARValue)),CustAccNum,AsOfMonth))


Hope it helps & happy Qliking!


Best

Jonas

136 Views
robert99
Valued Contributor II

Thanks Jonas   It was very helpful.

I (quickly) tried aggr but didn't get it to work but it does (or so far seems to) now based on your formula. I deleted my aggr attempts so was unsure what I did now. But it went from simple to complex and then I gave up.

But I'm happy now and feeling positive (but not for the first time). I just need to now try it on the more complex debtors overdue for 30/60 days etc. I will report back on how it goes

And what I'm trying to show is debtors outstanding over time for say over 30 days, over 60 days etc but without the negative credit balance in debtors. As it was completely distorting the outstanding balances. For example outstanding over 90 days sometimes showed a big credit balance. The data was correct but my expression wasn't.

Thanks Robert 

0 Likes
136 Views
DJT
New Contributor

Great article. Quick question to help me fully utilize and understand these capabilities:

In the below example from Henric, what are the rules regarding what can be written where I've coloured in red? (Are you only allowed one aggregation function? Can you do any arithmetic? Do you need as many $()'s as there are functions?)

I tried to alter the expression to sum sales if the date is on or before the last but one date in the dataset, but it doesn't appear to allow this alteration.

 

Henric's example:

Sum( {$<Date={"<=$(   =Max(Date)   )"}>} Sales)

 

My alteration (doesn't work)

Sum( {$<Date={"<=$(   =Max(Date) -1  )"}>} Sales)

 

0 Likes
69 Views