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:
-
- 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.
- Determine the function that will return the value you want. In this case, we can use:
It’s up to the developer and the specific requirement of the application to decide which function to use.
- 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.
- So,thefinal expression should look like the following:
- Sum({$<Year = {$(=Max(Year) – 1))}>} Sales
- 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:
- “=” – This is the one we have been using and what it does is simply re-deifine the selection for a certaing field.
- “+=” – This operator implicitly defines a union between the selected field values and the ones we speecify next.
- “-=” – This operator implicitly defines an exclusion of the values we specify from the values the user has selected.
- “*=” – 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.
- “/=” – 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.
This is a very handy set of tips but when dealing with time selections I find that selecting based on “Year – 1” tends to be problematic. I like to use the current selections to find the selected date range and subtract a year from that. This way I can more precisely override the selection on ALL date dimensions, something like this
Sum({$=’ & AddYears(Min(MonthStart),-1) & ‘}ExtendedPrice )
So I am selecting the specific order dates so if the user does something weird with the selections I still get the answer I was trying to achieve, in this case it was Prior MTD
Hi Chris, thanks for stopping by and leaving your comment. Although I must say I don’t quite understand your approach. I performed a little test and the expression returned null, it didn’t even recognized the syntax. I’m assuming ‘MonthStart’ is a date field and not the function we all know? Also, where do you specify which field you are assigning the value to? I think there are certain parts missing in the set expression but woul be glad to be proven wrong, since I recognize this as something new I can learn from.
Thanks,
Mike.
Hi Mike,
Excellent explanation. Regarding previous quarters, months and weeks, in my time dimension I generate an ID for each unique quarter, month and week using an AutoNumber. I then simply use those ID’s to retrieve the previous value instead of having to do all sorts of complex calculations. For example:
Quarter; Quarter ID
———————–
2009-Q4; 1
2010-Q1; 2
2010-Q2; 3
2010-Q3; 4
Now if I want to retrieve the previous quarter for 2010-Q1 I only need to take the max(ID) and subtract 1, e.g.
sum( {$ } sales)
Kind regards,
Barry
Rereading my comment, I see that set analysis expressions are not correctly parsed. All the code between angle brackets is removed. That would explain Chris’ strange syntax as well 😉
Thanks Barry! I use a very similar approach when using Set Analysis for previous months/quarters. That will be my next topic.
As for the syntx on the comments, I will check it out and see if there is something I can do to fix it. Thanks!
Mike.
[…] have already reviwed Set Analysis Syntax here and the concept of Dynamic Record Sets here. So now, we will combine the acquired knowledge to deliver a real-life application of Set […]
Great Blog.
I’ve learned so much.
Thanks.
Great post…Thanks a lot…
This is very useful information. I was wondering if this syntax could be used with a Master Calendar? I currently have a Master Calendar in my script to show – CalendarMonthAndYear… but when I write the following I return a null.
count({$< CalendarMonthAndYear={$(=Max(CalendarMonthAndYear)-1)}Distinct IncidentID)
@Mike, First of all I would like to thank you for the great examples of using Set Analysis.
@Jennifer – I assume you have a field in your Master Calendar for CalendarMonthAndYear. If you put a text box on your sheet and put
=(CalendarMonthAndYear – 1) is your result as expected?
A way that worked for me in a similar situation (think I got the idea off this blog actually) was to add ID’s to my Master Calendar. i.e. Sequential YearID and Sequential MonthID and just concatenate them.
Now instead of comparing to CalendarMonthAndYear you compare to CalendarMonthAndYearID.
(For a sanity check: put a table on your front-end with CalendarMonthAndYear and CalendarMonthAndYearID and make sure the ID runs sequential and also add a textbox to see if you put CalendarMonthAndYear -1 whether it gives you the required result).
Ofcourse there are other ways – i.e. using AddMonths(Year, -12) in concatenation – but I find the above nice on different systems to avoid “date calculation related problems”.
hi Mike,
thank you so much for this excellent post. however, I have tried to implement it in my own model, but it does not seem to work..
I used the expession
Sum({$} SlsQtymT)
and it states ‘expression OK’, but the outpu are zeros where they should not be, and also the part
$(=max(YEAR.MONTH) – 1)
of the expression shows grey (instead of the black font of the rest of the expression) and in italics;
could you help?
kind regards,
joris