Note About Dollar-sign Expansion

Katrina, from the Marketing Group, came to me yesterday with a QlikView document she was working on. It had three scatter plot charts arranged side-by-side and she wanted to make the side-by-side comparison easier by making them all have a similar Y-axis. She knew she wanted something for the static max property of the charts so that each would have the same maximum value for the Y-axis. She knew the basic expression to calculate the maximum value but the charts have a cyclic or cycle-group in the dimension and she needed an expression that would work with the cycle group.

I knew that she needed a dollar sign expansion (to handle the cycle group) and an aggr function (to find the maximum value by dimension) so I scribbled an expression like this on a sheet of note paper and Katrina left to try it out:

Max( aggr( your-calculation-of-y-value, $(=getcurrentfield( your-cycle-group-name )))

An hour later, I got an email from Katrina saying that the expression wasn't working. I decided to take a closer look at her document. The cycle group dimension in the charts is named “Scatter ExErr”. It contains a space or blank in the middle of it—so when it is used in an expression it must be surrounded by square brackets like this [Scatter ExErr]    (Note that double-quotes should work as well as square brackets.)

But, the expression still wasn't working correctly and it returned a null value. When I examined the field names used in the cycle group I saw that some of those also contained a space inside the field name. When you use a dollar sign expansion with a getcurrentfield function in your expression you can think of it as though the dollar-sign expansion becomes the field name before QlikView calculates the expression. Since the field name contains an embedded space then it must also be surrounded by square brackets and in this expression that means surrounding the dollar-sign expansion in square brackets. Katrina’s expression now has two pairs of square brackets and looks something like this:

Max( aggr( your-calculation-of-y-value, [$(=getcurrentfield([Scatter ExErr] )] ))

In documents that I develop, I avoid putting embedded spaces into any names that are not actually visible in the charts and I use an underscore character if it is needed to make a field or object name easier to read. If you decide to name things with names containing embedded spaces remember that you usually have to surround the name with square brackets or double quotes when the name is used in a function or expression.

★★★