Dynamic Record Sets

In Part II of The Series, I wrote about the general syntax of a Set Expression and provided some basic examples using Set Modifiers with explicit field value definitions. Now, our next step will be about making our desired record set dynamic and based on the user’s current selections, that is, using an Implicit field value definition.

We will cover the following scenarios:

• Obtain the record set of the Previous Year, Quarter or Month based on user’s selections. This is normally called, “Point In Time Reporting”.
• Get the current record set defined by the user and add/remove specific values using different assignment operators.

Point In Time Reporting based on user’s selection

As you may recall from a previous post, you can explicitly define the record set that certain expression uses. For instance, if you want to get the Sales of year 2008, you would simple use Sum({$<Year = {2008}>} Sales). But what if you want the year to be the immediate previous of whatever the user selects? Instead of hardcoding it with 2008, we will use an aggregation function to define the field value we want and accomplish that specific requirement. Follow this steps:

1. 1. Determine the function that will return the value you want. In this case, we can use:
      • Max(Year) – 1. That way, if the user selects 2009, our function will return 2008. Even more, if the user selects 2008 AND 2009, or makes no selection at all, we will still get the value for the previous year based on the last possible year (or Maximum number).
      • Some people like to use the function Only(Year) – 1, and that will give you the correct result, but only if the user has ONE selected value in the Field Year, it returns Null for any other scenario.

It’s up to the developer and the specific requirement of the application to decide which function to use.

1. Next,andonceweknow for sure what value the expression is returning and have tested it for different selections, we can use it asthesetmodifierinourfinal expression. The way we place a function as a Set Modifier is as follows:
   • $(=Function()). As you can see, it is just like using a variable (previous post of this Series), but with an equal sign.
2. So,thefinal expression should look like the following:
   • Sum({$<Year = {$(=Max(Year) – 1))}>} Sales
3. The same concept works with Quarters, Months, Days, etc. But, when you want to use it for Months, for example, you cannot expect Max(Month) – 1 to work for every month. What value will it return when the user selects January? It will return zero, which is not what we need. Although you can easily build an expression that returns the number 12, instead of zero, in the next post I will provide an even simpler solution for this inconvenient.

Add/Remove Values to the Already Selected Record Set

So far, we’ve only used Set Expressions to define new selections, disregarding what the user has already selected. However, in some cases we will need to modify the record set specified by the user by adding or removing certain values. To do that, we need to use a different assignment operator. The different assignment operators are:

1. “=” – This is the one we have been using and what it does is simply re-deifine the selection for a certaing field.
2. “+=” – This operator implicitly defines a union between the selected field values and the ones we speecify next.
3. “-=” – This operator implicitly defines an exclusion of the values we specify from the values the user has selected.
4. “*=” – This operator is used to define the record set based on the intersection between what the user has selected and the values we specify. That is, the resulting record set will be the values that “intersect” or are present in both the user’s selection AND the values we specify in our Set Expression.
5. “/=” – This one is used to define a symmetric difference (XOR), and the resulting record set will contain the values that are present in either one set but not in the other.

Let’s see some examples to better understand it.

• Sum({$<Year += {2007, 2008}>} Sales) This expression will return the sales for the years the user has selected AND the years 2007, 2008.
• Sum({$<Product -= {‘Product X’}>} Sales) This expression will return the sales for the products the user has selected excluding Product X.
• Sum({$<Product *= {‘Product X’}>} Sales) returns the sales for the current selection, but only for the intersection of currently selected products and the Product X.
• Sum({$<ProductNumber *= {“48*”}>} Sales) returns the sales for the current selection, but only for the intersection of currently selected products and all the product which number begin with 48.

Now that you’ve seen these two topics, you can start combining both of them in your set expressions. The options you have are unlimited.

I hope you have found this post useful. If so, please share with other people. Also, you are welcome to write your comments in the form below!

Update 2019/04/25: If you found this useful, you may want to take a look at the Set Analysis Cheat Sheet and invaluable resource for Qlik Developers.

Qlik Set Analysis Cheat Sheet. By Miguel Ángel García