Covering concatenated value-loops, dollar-sign expansion, nested dollar-sign expansion, and variable parameters!
As a quick reminder, a mathematical series is simply a sum of a sequence of numbers. For instance, the series 
simply represents the sum of all whole numbers from 1 to 5 (1 + 2 + 3 + 4 + 5 = 15). Series (which, by the way, is both the singular and plural form of the word) are useful tools in math. There is no native series function in QlikView; however, this very lack presents a golden opportunity for learning. In this exercise, where we will create series functionality from scratch, we will explore some of the most powerful tools available in our QlikView toolbox.
simply represents the sum of all whole numbers from 1 to 5 (1 + 2 + 3 + 4 + 5 = 15). Series (which, by the way, is both the singular and plural form of the word) are useful tools in math. There is no native series function in QlikView; however, this very lack presents a golden opportunity for learning. In this exercise, where we will create series functionality from scratch, we will explore some of the most powerful tools available in our QlikView toolbox.Concept 1: Value Loops
The valueloop(x,y) function is, along with valuelist(x,y), one of the two synthetic dimension functions available to us in QlikView. What do we mean by "synthetic" dimension? This is simply a dimension that does not exist in the data model—it is created on-the-fly. For instance, creating a straight table with valueloop(1,5) as a dimension will result in a table that looks as follows:
While a value loop has certain limitations (such as that both parameters must be provided in simple numeric format, or as variables that do not require any calculation), they can, for the most part, be treated the same as regular dimensions. One of my favorite things to do with a value loop is to use one as the third parameter of a concat() aggregation function. Doing so will result in the concat() aggregation running once for each value of the loop. For instance, the following syntax concat('A', ';', valueloop(1,5)) will result in a string that looks as follows: A;A;A;A;A. Of course, you can use the same value loop in the first parameter of the concat() function, as well, which is precisely the first step on our journey to creating a mathematical series in QlikView.
Let's create a new variable in QlikView in the Variable Overview. Define the variable as follows:
Now let's create a text object that displays the contents of this variable, by writing an expression in the text object such as =vMyVariable. You will see that the contents are 1+2+3+4+5. Notably, this is simply a text string and does not evaluate to 15. At least, not yet.
Concept 2: Dollar-Sign Expansion
Dollar-sign expansion is the method by which you can tell QlikView to evaluate something as a QlikView expression, even if it does not recognize it as such by default. If we create a text object and populate it with a dollar-sign expansion of the variable we created above, such as =$(vMyVariable), the result will now evaluate to 15.
So, we now have a working math series in QlikView! However, it's somewhat limited in that both the definition of the series and the parameters have been hard-coded into the variable.
Concept 3: Variable Parameters
The next concept we will explore is one that is often overlooked by QlikView developers (probably because it is omitted from virtually all training courses). However, this concept is truly one of the most powerful in QlikView, and is what we will use to make our series variable reusable with different parameters without having to create multiple variables. That concept is variable parameters.
A variable parameter, which is defined in the variable as $1, $2, $3, etc., allows a regular QlikView variable to be treated like a function. For instance, let's create a variable called vAddTwo and define it as follows: $1 + $2. Now let's create a text object and populate it with the following expression: =$(vAddTwo(3,4)). The text object will display 7 as a result!
Syntax note: variable parameters can only be used with variables whose definitions do not begin with an '=' sign.
Now let's apply this concept to the series variable we created above. Replace the 1 and 5 in the variable definition with $1 and $2, respectively. And remove the '=' sign. So the new variable definition would be given as:
Let's see what happens when we use traditional dollar-sign expansion on this variable while passing in parameters. Create a text object with an expression defined as follows: =$(vMyVariable(1,5)). The result is the text string 1+2+3+4+5. But why didn't the variable evaluate? The answer to that is explored in our next concept.
Concept 4: Nested Dollar-Sign Expansion
The dollar-sign expansion that we performed in the last step above was simply used by QlikView as part of the syntax for variable parameters. Meaning, if you were to create a text object with expression =vMyVariable(1,5), without dollar-sign expansion, QlikView would balk, because "vMyVariable" is not a recognized native function. So the dollar-sign expansion was simply used to tell QlikView to treat that variable as a custom function. However, in order to actually evaluate the result of this variable (which is a text string), we will need another dollar-sign expansion around the entire thing.
Let's try to create a text object with an expression as follows: =$($(vMyVariable(1,5))). Why did this evaluate to null? Because the outer dollar-sign expansion is looking for a variable to be returned by the result of the inner operations. Recall that the syntax for a simple dollar-sign expansion is $(VariableName). However, in our case, because the inner operation returned a text string, the outer expansion was forced to try to evaluate $(1+2+3+4+5). Since "1+2+3+4+5" is not the name of a variable, the expansion failed.
In order to accomplish our task, we will need to use another type of dollar-sign expansion. When you include an '=' sign between the parentheses in dollar-sign expansion, QlikView will attempt to evaluate whatever follows as a regular QlikView expression. The syntax is $(=Expression). In our case, we would write this in our text object as =$(=$(vMyVariable(1,5))). Since “1+2+3+4+5” is a valid QlikView expression, the correct result of 15 is finally returned!
Putting It All Together
The below application demonstrates all the concepts that are discussed above, and more. Series 1 – 3 in the left column show three different series implemented with the above techniques. The "Custom Series" and "Advanced Series" use dollar-sign expansion on steroids—they demonstrate how you can actually redefine the series themselves on the fly. And the Advanced Series further shows how you can introduce a true math variable (x) to change the results of a series with a slider.
If you have any questions on these advanced concepts, please share them with us below and we'll do our best to answer them. And, as always, happy Qliking!
Hi,
Thanks for posting this blog. I am curious to understand to what extend or in what situation it could be beneficial to implement the calculation of mathematical series in QlikView. Your examples are working autonomous whereas the power of QlikView lies in analyzing, filtering and aggregating the whole data model on the fly. Static calculations and mathematical model simulations are much easier in Excel, don’t you think?
There are many things you can do and achieve with Qlik, see also Stephen Redmond’s christmas tree, but it is important in my opinion to know what should or should not be implemented with Qlik. It would be helpful if you could include a real-case scenario where ValueLoop/ValueList/Mathematical Series in QV demonstrate why you believe they are powerful.
Thanks,
Martin
Martin, thanks for your comment. As the title says, this is an exercise in advanced syntax. The purpose was simply to teach concepts that are themselves important. Creating a mathematical series was a means to an end.
Best regards,
Vlad
Thank you very much for posting this. Concepts were addressed clearly and easily understandable.