Using Variables for Expressions

    Introduction

     

    Variables are memory addresses to save some data for a specific period of time. By default, variables are used to store text or numeric values. But, in QlikView, variables are able to store formulas as expressions. These expressions will store formulas to be used in different charts, others formulas and objects. Using this resource you will decrease maintenance effort, because it's possible to change a complex formula only once. At script level, variables are defined using either LET or SET commands. Variables can also be defined using Desktop's Interface through Variable Overview in Settings menu.

    When a same expression is used in different objects you can create a variable to store it's formula. Moreover, variables can be defined with parameters and act as a typical function. So, you inform one or more values where variable will handle to execute a calculation.

     

    Creating Variables

    At script level use SET or LET commands. When using SET, no changes are made when a value is associate in variable. In other hand, if LET is used, QlikView will test and interpret it's content before associate it's value in variable.
    LET vResult1 = 1 + 1; SET vResult2 = 1 + 1; 
    VariablesSETandLET.PNG
    Using LET as statement, vResult1 variable will store a result of 1 + 1, therefore, 2, instead of '1 + 1' literal string. But, using SET to define a variable, result2 will store '1 + 1' literal string. Another way to add a new variable is use the Variable Overview (CTRL + ALT + V shortcut) window from Settings menu.
    VariablesWindow1English.PNG
    To define a new variable just click on Add button and fill a variable name. For this article vVariation was used as identification name. After click Ok, variable is showed in Window. To define a new value for it, just click on variable name and enter a formula in definition frame. If you don't click on variable name a new value will not fill a variable. For example, to add a value 1 to this variable, just type 1. It's not necessary use equal sinal (=) before value. For text values isn't necessary include string delimiters as single or double quotes.
    Beyond that is possible add formulas to variables using a regular expression as SUM(Sales). There's a difference between use equal sinal (=) or not. When a formula IS NOT using equal sinal (=) any dimension present in a chart will be use to break down result values from variable. However, if a equal signal (=) is used in variable definition, values from dimensions aren't gonna BE CONSIDERED. So, result will get all values (as a TOTAL parameter) from a field.
    Pay care!
    =SUM(Sales) // Sum all values from Sales field, disregarding any dimensions.
      SUM(Sales) // Sum all values from Sales field, breaking down for dimension values.
    To use a variable from any place where a expression is allowed, just enter a Equal Signal followed by variable name. But, this will work only when no formulas are stored in variables. When a variable needs to be interpreted, use $(vVariation) to execute the internal formula.
    VariablesWindow2English.PNG

    Creating Variables for Expressions

    Expressions in Variables can be defined by prefixing with equal (=) sign or without equal (=) sign. When variable expression is prefixed with equal (=) sign, for example, vTotalFreight = Sum(Freight), then this variable caculation will be performed globally, i.e. for entire data set. For demonstration how to use variable to store expressions, load the following sample data that will be used to compare actual year versus previous year.

      Note:  Replace commas in Qty field by dots if you're using English formating. 


    Vendas:

    LOAD * INLINE [

    City, Company, Id, Qty, TotalSale, YearSales

    Aachen, Gluderstedt, 10538, 1, '11,58', 2010

    Buenos Aires, Los Pantalones Magicos, 10782, 1, '8,19', 2010

    Buenos Aires, Los Espandrilos Fantasticos, 10531, 2, '7,9', 2010

    Lisboa, El Traige do Benfica, 10963, 2, '4,78', 2010

    London, The sharped dressed man, 10462, 1, '27,09', 2011

    Luleå, Urras Shop, 10524, 2, '7,49', 2011

    Madrid, La Moda Alucinante, 10281, 1, '9,9', 2011

    Madrid, La Moda Alucinante, 10917, 1, '26,2', 2011

    México D.F., La Tienda de la Esquina, 10308, 1, '2,12', 2012

    México D.F., Los Sombreros Gigantes, 10259, 1, '4,37', 2012

    México D.F., Don Balón, 10676, 2, '6,48', 2012

    Portland, Too Hot 4U, 10665, 1, '8,03', 2012

    Torino, Il Pantaloni di la Cammorra, 10807, 1, '20,11', 2012];

    After sample data is loaded in memory, create a new variable to calculate total sales for maximum year. To do that, press CTRL + ALT + V shortcut or access Variables Overview from Settings menu. Click Addbutton and fill the variable name as vVariation. Click on variable name and use definition to enter the following formula:

    Sum({$<YearSales={'$(=Max(YearSales))'}>} Qty * TotalSale) 

    VariablesWindow3English.PNG

    VariablesWindow4English.PNG

    From now is possible use vVariation in any place where a expression is valid. For example, you can create a new bar chart and add City dimension. For expression, just call vVariation to show sales for last year. Any change on expression can be done updating variable formula. Furthermore, variables can accept parameters where input values are filled. For example, try improve last variable to get year (actual or previous) using a new parameter. Parameters in variables are defined using $1, $2, $n symbols. Using last variable, change its structure as:

    Sum({$<YearSales={'$(=Max(YearSales)-$1)'}>} Qty * TotalSale)

    Using this code, $1 will be filled as 0 (zero) or 1 (one), or even another value. To get actual year just fill this parameters with 0. To get previous year, $1 will be filled with 1 (one) because MAX(YearSales) - 1 is last year minus one. Therefore, previous year. Two year ago is defined using MAX(YearSales) - 2, or $1 = 2, and so on. To call this variable from any place where a expression is valid, just use$(vVariation(0)), where 0 is a parameter value. To get sales for last year just call it using $(vVariation(1)).

    VariablesWindow5English.PNG

    Now, is possible calculate variation from previous year to last year just call this variable 3 times. For example:

    =($(vVariation(0)) - $(vVariation(1))) / $(vVariation(1))

    That mean: (ACTUAL YEAR - PREVIOUS YEAR) / PREVIOUS YEAR

      Remember:  Multiples parameters can be defined in a variable. 

     

     

     

    Need more?

    www.qknow.com.br