Qlikview Parameterised Variables

    The Qlikview Variables are quite cool and they can be used as little functions too. For example, the below variable declaration gives the flexibility to use any simple aggregation functions on any fields in the model combined with, set analysis.

    The expression to be declared in the script is as follows


    Let vCalculateExpression=’$’&1&'({<$’&’2={“>=$’&'(=Date($’&'(vFromDate),”DD/MM/YYYY”))<=$’&'(=Date($’&'(vToDate),”DD/MM/YYYY”))”}>} $’&’3)’;

    Please take a note of the ”..The extra single quote acts as an escape character for the quote you want around the text literals. The parameters are passed as $1,$2,$3..etc.

    The variable can be called from the qlikview expression editor as follows.. The flexibility this expression gives is that you could choose any of the fields can be used as filters

    Sum

    $(vCalculateExpression(Sum,[Sale Date],[Sales Value]))

    Count

    $(vCalculateExpression(Count,[Sale Date],[Sales Value]))

    Average

    $(vCalculateExpression(Avg,[Sale Date],[Sales Value]))

    Although , the above expression might look a bit daunting in the first go, it is a very simple expression. the parsed text will look as follows.Please note that I have deliberately left the set analysis hardcoded in the expression but it can very well be passed as another parameter to the variable.

    sum({<[Sale Date]={“>=01/12/2015<=31/12/2016”}>} [Sales Value]).. simple! right?

    if you were to write the variable in the UI , you would write it as follows
    for a variable X, the value is written as $1({<$2={“>=01/12/2015<=31/12/2016”}>} $3). The variable overview editor might yell at you in all red but it will work.

    Qlikview Variable Overview

    ParameterisedVariableQVEdit.png

    I have attached sample application to this page,Hope you like it.