How to Report Sales for the Six Months Previous to This One

    A field for Year & Month is needed to get the right results when the six-month period extends back into the previous year. The field contents must be numeric so the field

    Year(OrderDate) & num(Month(OrderDate),'00') is loaded as YearMonthNum. OrderDate is a referenced date. See also How to Report Sales for Last Year and This to Date.

        
          

    Load YearMonthNum

                    
                Load-YearMonthNum.png
          

    The QT Sales Demo script has been edited in QlikView to add a numeric field

    • Year(OrderDate) & num(Month(OrderDate),'00') as YearMonthNum
        
        
        
          

    Create New Filter

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

    Name and Add Fields

                    
                Name-and-Add-Fields.png
          
    1. Enter a Name for your filter
    2. Enter a Description (optional)
    3. Click on Add
        
        
        
          

    Open "Select field" Window

                    
                Open--Select-field--Window.png
          

    Click on the browse button at the extreme right of the Name field.

        
        
        
          

    Choose Fields

                    
                Choose-Fields.png
          
    1. Select the Connection to the QlikView document that contains the field you want
    2. Select YearMonthNum from the Select field dialog window list
    3. Click on the OK button to conclude the field selection process
        
        
        
          

    Define First Field Value

                    
                Define-First-Field-Value.png
          
    1. Click on the green '+' button in the lower left of the Field dialog window
    2. Enter the the year(now()) & num(month(now()),'00') expression in the Value cell
    3. Select IsNumeric so that the box is checked
    4. Select Evaluate so that the box is checked
        
        
        
          

    Define Additional Field Values

                    
                Define-Additional-Field-Values.png
          
    1. Add the expressions: year(AddMonths(now(),-n)) & num(month(AddMonths(now(),-n)),'00') where n varies from 1 to 6 by clicking on the green '+' button and entering the expressions into the new Value cell
    2. Select IsNumeric so that the box is checked for all of the new expressions
    3. Select Evaluate so that the box is checked for all of the new expressions
    4. Click on OK to conclude Field Value definition
        
        
        
          

    Save Filter Dialog Window and Close

                    
                Save-Filter-Dialog-Window-and-Close.png
          

    Click on the Save and Close icon in the Actions group of the tool bar

        
        
        
          

    Create New Excel Report

                    
                Create-New-Excel-Report-Template.png     
          
    1. Select Reports in the lower left pane
    2. Excel reports will be selected automatically in the upper left pane
    3. Select Excel Report in the New group of the tool bar
        
        
        
          

    Enter Basic Report Information

                    
                Enter-Basic-Report-Information.png
          
    1. Enter a Name for your Excel report
    2. Enter a Description (optional)
        
        
        
          

    Add Filter to Report

                    
                Add-Filter-to-Report.png  
          
    1. Select the Filters icon in the Show group of the tool bar
    2. Click on Add in the Filters group of the tool bar
    3. Select the Lat Six Months Dynamic filter from the list presented in the Select Filters dialog window
    4. Click on OK to conclude filter selection
        
        
        
          

    Create New Excel Report Template

                    
                Create-New-Excel-Report.png
          

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

        
        
        
          

    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 the QlikView document that contains the field you want
    2. Select the YearMonth field for the level in the Select Levels dialog window list
    3. Click on the OK button to conclude adding a level
        
        
        
          

    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 that contains the object you want
    2. Select the Top 3 Countries - CH296 object for the table in the Select Objects dialog window list
    3. Click on the OK button to conclude adding a table
        
        
        
          

    Embed Table in Template

                    
                Embed-Table-in-Template.png       
          

    Drag the CH296 - Top 3 Countries node token into the template and drop it onto an empty cell, B6 in the example

        
        
        
          

    Embed Level in Template

                    
                Embed-Level-in-Template.png      
          

    Drag the YearMonth_Level node token into the template and drop it onto three, empty, vertically sequential cells in the same column as or in a column to the left of the column containing the CH296 tag, A3 - A5 in the example

        
        
        
          

    Adjust Defined Level Range

                    
                Adjust-Defined-Level-Range.png  
          

    Reposition the YearMonth_Level tags as shown in the image above

        
        
        
          

    Embed YearMonth Tag as Level Heading and Format YearMonth Content

                    
                Embed-YearMonth-Tag-as-Level-Heading-and-Format-Ye.png
          
    1. Expand the YearMonth_Level node by clicking on the '+' to its left
    2. Drag the YearMonth node token into the template and drop it onto and appropriate empty cell to act as a level heading
    3. Format the cell content with the tools in the Excel tool bar
    4. Uncheck the Keep Source Formats box
    5. Enter the 'mmmm yyyy' formatting code, or some other and click on OK at the bottom right of the dialog window
        
        
        
          

    Add Formula

                    
                Add-Formula.png
          
    1. Right click the Formulas node
    2. Click on Add formula to open the Add formula dialog window
    3. Enter "This Month" as the formula Name
    4. Enter a Description (optional)
    5. Enter the =text(date(now(),'MMM yyyy')) expression in the Formula field
    6. Click on OK to finish defining the formula
        
        
        
          

    Embed "This Month" formula in Template as Dynamic Heading

                    
                Embed--This-Month--formula-in-Template-as-Dynamic-.png
          

    Drag the This Month node token into the template and drop it onto an appropriate empty cell, B2 in this case

        
        
        
          

    Add Static Part of Heading

                    
                Add-Static-Part-of-Heading.png
          
    1. Enter "This Month" in cell B1 above the This Month tag
    2. Format the two cells the same
        
        
        
          

    Inspect Resulting Report

                    
                Inspect-Resulting-Report.png
          

    Note that this month is May and November is six months previous.