How to Report Sales for Last Year and This to Date

    A filter employing a simple QlikView formula will be created and used to generate a report. The filter will dynamically select for data this year and last, with no need to modify the filter as time passes. See also How to Report Sales for the Last Six Months and This to Date.
       
         

    Create a New Filter

                   
                Create-a-New-Filter.png
         
    1. Select Filters in the lower left pane
    2. Filters will be selected in the upper left pane automatically
    3. Click on Filter in the New group in the tool bar
       
       
       
         

    Name the Filter and Add One or More Fields

                   
                Name-the-Filter-and-Add-One-or-More-Fields.png
         
    1. Enter a Name for your filter
    2. Enter a description (optionally)
    3. Click on Add
       
       
       
         

    Select the Field

                   
                Select-the-Field.png 
         

    Click on the browse icon at the extreme right of the Field Name field.

       
       
       
         

    Select a Field

                   
                Select-a-Field.png
         
    1. Select the Connection to the QlikView document that contains the Field you want
    2. Select Year from the list in the Fields dialog window
    3. Conclude the selection by clicking on OK
       
       
       
         

    Add Formulas to Field

                   
                Add-Formulas-to-Field.png       
         
    1. Click on the green '+' in the lower left corner of the dialog window
    2. Enter a formula in the Value box
    3. Check the IsNumeric box
    4. Check the Evaluate box
    5. Click on the green '+' in the lower left corner of the dialog window again
       
       
       
         

    Add Another Formula to Field

                   
                Add-Another-Formula-to-Field.png           
         
    1. Enter a formula in the Value box
    2. Check the IsNumeric box
    3. Check the Evaluate box
    4. Click on OK
       
       
       
         

    Save Filter

                   
                Save-Filter.png
         

    Click on the Save and Close icon

       
       
       
         

    Create New Report

                   
                Create-New-Report.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
       
       
       
         

    Name and Describe

                   
                Name-and-Describe.png
         
    1. Enter a Name for your report
    2. Enter a Description (optional)
       
       
       
         

    Add Prepared Filter to Report

                   
                Add-Prepared-Filter-to-Report.png   
         

    The order of the Add Prepared Filter to Report and the Create New Report Template phases is a matter of choice and unimportant.

    1. Click on the Filters icon in the Show group of the tool bar
    2. Click on the Add icon in the Filters group of the tool bar
    3. Select the Dynamic Filter for the Last Two Years filter from the list in the Select Filters dialog window
    4. Click on OK
       
       
       
         

    Create New Report Template

                   
                Create-New-Report-Template.png   
         

    Select New in the Template group of the tool bar

       
       
       
         

    Open Select Objects Window

                   
                Open-Select-Objects-Window.png
         
    1. Right click on the Tables node
    2. Click on Add objects
       
       
       
         

    Add QlikView Object as Table

                   
                Add-QlikView-Object-as-Table.png
         
    1. Select the Connection to the QlikView document containing the object you want to add
    2. Select the Top 3 Countries - CH296 from the list in the Select objects dialog window
    3. Click on OK to conclude the selection
       
       
       
         

    Embed Table in Template

                   
                Embed-Table-in-Template.png     
         

    Drag the Top 3 Countries - CH296 node token into the template and drop onto an empty cell

       
       
       
         

    Open Select Levels Window

                   
                Open-Select-Levels-Window.png 
         
    1. Right click on the Levels node
    2. Click on Add levels
       
       
       
         

    Add Level to Template

                   
                Add-Level-to-Template.png
         
    1. Select the Connection to QlikView document that contains the Field that you want
    2. Select Year from the list presented in the Select Levels dialog window
    3. Click on OK to conclude the selection
       
       
       
         

    Define Year_Level Range

                   
                Define-Year_Level-Range.png
         
    1. Drag the Year_Level node token into the template and drop it onto three, empty, vertically sequential cells. The three cells have to be in the same column as, or in one left of, the tags and content to be cycled in the level
    2. Reposition the Year_Level tags as necessary. In this example, we leave an empty row above the CH296 tag for a heading
       
       
       
         

    Add Year Tag as Level Cycle Heading

                   
                Add-Year-Tag-as-Level-Cycle-Heading.png
         
    1. Expand the Year_Level node by clicking on the '+' to its left
    2. Drag the Year node token into the template and drop it onto an appropriate empty cell, A2 in this case
    3. Select the Toolbar icon in the view group of the NPrinting tool bar if the Excel tool bar is not visible
    4. Format the Year tag cell using Excel formatting tools
    5. Click on the upper part of the Preview icon to have a look at the resulting report
       
       
       
         

    Inspect Results

                   
                Inspect-Results.png                 
         

    Note that though there are three years of data available, only this year's and last year's data are displayed.

       
       
       
         

    Save Template

                   
                Save-Template.png
         

    Click on the Save As icon to open the Save template as dialog window

       
       
       
         

    Complete Template Saving Operation

                   
                Complete-Template-Saving-Operation.png
         
    1. Enter Template name in the File name field
    2. Click on the Save button to conclude the operation
       
       
       
         

    Save Your Report

                   
                Save-Your-Report.png
         

    Click on the Save and Close button.

       
       
       
         

    Save NPrinting Project File

                   
                Save-NPrinting-Project-File.png
         

    Click on the floppy disc icon to save all your work in the NPrinting Project file, and your done.