Download Set Analysis Cheat Sheet

x

Double Dollar-sign Expansions

 

In both Qlik Sense and QlikView, dollar-sign expansions play an essential role.

If you define a variable in the application to store an expression — since the expression then can be used in multiple charts — you can use dollar-sign expansion first to expand the expression text and then the parser evaluates that expression.

Let’s look at an example.

I can define a variable in the Qlik Sense application to store a fiscal year value.

vMaxYear

=max([Fiscal Year]) -1

Since I have used = sign in the expression, the value of the year will be evaluated immediately, and the value gets recalculated with every selection.

Now, in Set Analysis, a variable can’t be used without dollar-sign expansion.

Also, dollar-sign expansion with expression within a Set Analysis syntax requires = sign.

<Field Name = {“>=$(=max([Fiscal Year]) -1)”}>

As long as I have defined variable with = sign preceding the expression, I can use it in the Set Analysis expression with a dollar-sign expansion.

Sum ({<[Fiscal Year] = {“>=$(vMaxYear)”}>} ARSalesPerDay)

This Set Analysis expression will show average sales per day based on the fiscal year use has selected. If the selected year is 2019, it will show average sales for 2018 and onwards.

Here’s the caveat.

What if the variable expression lacks = sign?

Now, remember that the Set Analysis syntax requires dollar-sign to use a variable within an expression, and the expression itself requires = sign within the dollar-sign in the Set Analysis expression.

Enter double dollar-sign expansions.

We have to use outer dollar-sign expansion to expand a string that contains just equal sign and the inner dollar-sign expansion to expand the expression stored within the variable definition.

So, assuming that the variable definition lacks equal sign, our Set Analysis syntax will require double dollar-sign expansion magic.

Sum ({<[Fiscal Year] = {“>=$(=$(vMaxYear))”}>} ARSalesPerDay)

Let’s dissect this expression as a two-step process.

Sum ({<[Fiscal Year] = {“>=$(=)”}>} ARSalesPerDay)

This is the first dollar-sign expansion that simply expands = string.

In the second step, let’s add inner dollar-sign expansion to expand the variable definition.

Sum ({<[Fiscal Year] = {“>=$(=$(vMaxYear))”}>} ARSalesPerDay)

Beccome a FREE member to watch other tips & tricks videos

 

Join our Academy and let us help you take your Qlik skills to the next level!

 

If you enjoyed this article, get email updates (it’s free).

* indicates required
About the author

Leave a Reply