Set a QlikView Variable to a Specific Value Using Filters

    Description: Use NPrinting filters to set the value of a variable in a QlikView document. This tutorial uses SalesDemo.qvw

    You have to create the needed variables in your QlikView application and refresh the objects list before to start this tutorial.

    Begin Creating New Excel Report

    a4aa7be9-b018-4365-81ae-f75960529287_display.png

     

    1. Select Reports in the lower left pane
    2. Select Excel reports in the upper left pane
    3. Click on Excel Report in the New group of the tool bar

    Identify New Report

    f087847e-88c6-43ef-bcbe-5b32a45df7f5_display.png
    1. Enter Set QlikView Variable Value with Filters as report Name
    2. Enter a clarifying Description if need be (optional)

    Begin Creating New Filter

    37e8cf02-958a-4e7c-883f-edeec4baeebb_display.png
    1. Click on the Filters icon in the Show group of the tool bar
    2. Click on the New icon in the Filters group of the tool bar

    Identify Filter

    3c12916c-a9a1-4c26-a479-8ac44e6d81fc_display.png
    1. Enter Set vLast Year to 2013 and vCurrentYear to 2014 as filter Name
    2. Enter a clarifying Description if need be (optional)

    Open Variable Configuration Window

    af6580ac-654d-4d7d-8841-27e93cd9af75_display.png
    1. Select the Variables icon in the Show group of the tool bar
    2. Click on the Add icon in the Variables group of the tool bar

    Open Select Variable Window

    d0af1d94-4634-41d8-8911-eb5dcb60667b_display.png


    Click on the browse button at the extreme right of the Name field in the Variable window

    Select Variable

    cb5f60a5-4b1b-40b4-b56d-837253a03409_display.png
    1. Select the Connection to the QlikView document that contains the Variable you want
    2. Select the vLastYear variable
    3. Click on the OK button

    Set Variable Value

    1a702c9a-62d3-421f-bf93-dfb9f1e925d5_display.png
    1. Enter 2013 in the Value field
    2. Put a check mark in the Calculate box only if you enter a formula in the Value field, as it will need to be evaluated by QlikView. A constant has been entered here, so leave the Calculate box unchecked
    3. Click on the OK button

    Add Another Variable

    e82f8196-c6b2-41d8-a846-9d77f5a8efef_display.png
    1. Click on the Add icon again
    2. Click on the browse button again

    Set Another Variable Value

    54798c33-6d3a-4268-a468-d86d5e4e7207_display.png
    1. Select the Connection to the QlikView document that contains the Variable you want
    2. Select the vCurrentYear variable
    3. Click on the OK button

    Set vCurrentYear to 2014

    30d37f11-e484-4839-b36b-c355031d7efa_display.png
    1. Enter 2014 in the Value field
    2. Click on the OK button

    Save Filters


    5736a732-55f0-41e1-9cf7-cf8452d55e96_display.png


    Click on the Save and Close icon in the Actions group.

    Open New Template in Template Editor

     

    e9c83871-2105-4e2a-a479-33d63f4fac9f_display.png


    Click on New in the Template group of the tool bar.

    Open "Save template as..." Window

    7b4426dc-41d0-43b3-be59-9dc6c04f47c3_display.png


    Click on the Save As icon in the Actions group.

    Save Template

    b5632862-021d-4bc4-8c56-e1742c2a061b_display.png
    1. Make sure the Save template as... window has opened in the C:\NPrintingTraining\Templates\ folder. If not, browse to it.
    2. Enter a File name or accept the suggested one
    3. Click on the Save button

    Open Select Objects Window

    388c23e9-5c62-48fc-b4fe-e30ce8048075_display.png
    1. Right click on the Tables node
    2. Click on the Add objects button

    Select Object to Embed as Table

    422ce212-e5df-4d40-9d29-e618383ff207_display.png
    1. Select the Connection to the QlikView document that contains the object to be added as a table
    2. Select the Sales over Time by Category - CH251 object
    3. Click on the OK button

    Open Select Variables Window

    9b154eff-6e13-423b-a6dd-4fb93956c68d_display.png
    1. Right click on the Variables node
    2. Click on the Add variables button

    Select Variables to Be Added

    96c11fbd-1b82-4351-ace7-16f4480e4b2d_display.png
    1. Select the Connection to the QlikView document that contains the object(s) to be added as a table
    2. Select the vCurrentYear and vLastYear objects from the SalesDemo connection.
    Repeat 1. and 2. as necessary
    1. Click on the OK button

    Add Headings to and Embed Variables in Template

    b2f412a0-e629-4117-b258-294b72cb443d_display.png
    1. Enter the headings Last Year and Current Year into cells A1 and B1 respectively
    2. Drag the variables vLastYear and vCurrentYear and drop them onto cells A2 and B2 respectively

    Embed Table Columns in Template

    82663203-882c-4b5a-9354-12856f1b86cc_display.png

    1. Expand the CH251 - Sales over Time by Category node by clicking on the '+' to its left
    2. Drag the Category Name, =$(vLastYear), and =$(vCurrentYear) node tokens into the template and drop them onto cells B4, C4, and D4 respectively

     

     

    Change Headings

    cdc67b0e-396c-48f0-bc26-617fdad223eb_display.png

     

    Substitute the =$(vLastYear), and =$(vCurrentYear) headings with =B1 and =B2 respectively.

     

    Apply Formatting

    cd3ec6eb-a4d4-4432-8e15-a9ba546020ea_display.png

     

    Use Excel formatting tools to format headings and data.

     

    Run Preview

    c5c83c70-3ef3-459b-9511-8371c8b87516_display.png

     

    Click on the upper part of the Preview icon in the Actions group of the tool bar.

     

    Inspect Results

    405b0e67-d491-495d-bc6f-9c5e80d6babc_display.png

     

    You will see the data for 2013 and 2014 regardless of the year in which you launch the preview.

     

    Save Your Work in Template and Close Template Editor

    e04b3d09-a85b-4edb-8611-ab95d54b57a2_display.png

     

    Click on the Save and Close icon in the Actions group.

     

    Save and Close Report

    1c9000a3-6b64-4b2e-8cb8-cd2673daf089_display.png