Saturday, 29 April 2017 06:52

Qlikview: Dollar sign extension inside set analysis

Written by

In our last blog we showed how to use a Qlikview variable inside set analysis. We will replicate a similar scenario but this time, instead of using a variable, we will use an expression to first get the maximum date and then use that maximum date to restrict the set analysis expression to the maximum date

In set analysis, the maximum date expression needs to be evoluated first before the rest of expression is evaulated. We will use a dollar($) sign to achieve that. In Qlikview, this is called dollar sign expansion. Suppose that our normal charts looks like this

 

 

Suppose that our requirement is that we need to restrict the chart to whatever the maximum date we have available in the database. We can use the below set analysis expression to achieve this

 

sum({<prod_date = {"$(=date(max(prod_date)))"}>}Qty)

 

the expression $(=) is called dollar sign expansion. This will evaulate the expression inside the dollar before the rest of the expression is evaluated. The result is that we get the set analysis for the max date which is calculated inside the set analysis.

 

 

If you have any question related to this article, please ask in the comment section below

 

 

Friday, 28 April 2017 08:44

Qlikview: Using variable inside set analysis

Written by

I have recently been asked by many junior Qlikview developers on how to use variables inside Qlikview set analysis expression. Last week, one of our Qlikview consultant was also struggling with fetching the latest date by using variable. Finally I decided to write a short blog on how to use Qlikview variables inside set analysis

Using variables inside Qlikview is a bit ticky business. We could use equal, $ or simple variable name depending upon the usage of the variable. In this specific case, we have stored the latest date in the loading script and we want to use that variable in a set analysis expressions

Suppose a normal chart with all dates looks something like this

 

 

As you can see, it is using a normal expression of sum for the chart. Now if you want to use a variable inside the set analysis to restrict the chart to only the max date, a set analysis expression would become something like this

 

sum({<Month = {'$(max_date)'}>}Qty)

The resultant chart would look something like this

 

 

Please feel free to ask questions in comments if you have any question related to this blog