Qlik Community

Qlik NPrinting Documents

Documents related to Qlik NPrinting.

Select Top Ranks from QlikView and Use Them With Custom Excel Formulas

Support
Support

Select Top Ranks from QlikView and Use Them With Custom Excel Formulas

You will learn how to create a chart using a defined range of rows from a QlikView chart object in this tutorial. Avoid table expansion invalidating cell references in custom Excel formulas.

    
    
        
          

Create New Excel Report

                    
     Create-New-Excel-Report.png                           
          
  1. Select Reports in the lower left pane
  2. Select Excel reports in the upper left pane
  3. Select Excel Report in the New group of the function ribbon
        
        
        
          

Name Report and Create New Template

                    
        Name-Report-and-Create-New-Template.png                        
          
  1. Enter Show Top Ranks Excel Report as Name for your report and optionally a Description
  2. Select New in the Template group
        
        
        
          

Open Select Objects Window

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

Select Object

                    
        Select-Object.png                        
          
  1. Select the Connection to the QlikView document that contains the object that you want, SalesDemo in this case
  2. Select Top Products by Sales -  CH294
  3. Click on the OK button
        
        
        
          

Adjust Table Properties

                    
         Adjust-Table-Properties.png                       
          
  1. Make sure CH294 - Top Products by Sales is selected
  2. Enter the row range you want to import in the Custom Rows field
  3. Unflag the Expand Range Option
        
        
        
          

Drag Your Chart into Template

                    
         Drag-Your-Chart-into-Template.png                       
          

Drag your chart columns into the template. Make sure that there are at least 10 blank rows above the chart.

        
        
        
          

Mean Price Formula

                    
          Mean-Price-Formula.png                      
          

We are going to add a new column to calculate the mean price. We will use a simple formula that refers to the value in specific cells.

  1. Enter the Mean Price column heading
  2. Enter the formula to calculate the mean price
        
        
        
          

Expand Formula

                    
         Expand-Formula.png                       
          

Expand the formula in the Mean Price column for 10 rows

        
        
        
          

Format Mean Price

                    
         Format-Mean-Price.png                       
          
  1. Select the Mean Price Column
  2. Format the column as Currency
        
        
        
          

Add Gross Profit Total

                    
         Add-Gross-Profit-Total.png                       
          
  1. Select the 10th cell under the Gross Profit Sales heading.
  2. Enter the formula to calculate Total Sales. Note that you don't need to leave a blank row since NPrinting will not expand the table
  3. Format the total cells as you prefer
        
        
        
          

Choose Correct Format

                    
         Choose-Correct-Format.png                       
          
  1. Select Number
  2. Choose Currency in the category table
  3. Choose the symbol you need from the Symbol menu
  4. Click OK
  5. Add formatted totals also for "Total Sales" and for "Quantity"
        
        
        
          

Add GP% Formula

                    
           Add-GP--Formula.png                     
          
  1. Select the 14th cell under the GP% column heading.
  2. Enter the formula to calculate the mean GP%
        
        
        
          

Format Mean GP%

                    
          Format-Mean-GP-.png                      
          
  1. Right click on the cell containing the mean GP% formula
  2. Choose Format Cells.... The Format Cells window opens
  3. Select Number
  4. Choose Percentage in the category table
  5. Click OK
        
        
        
          

Preview

                    
          Preview.png                      
          

Click Preview in the template ribbon bar

        
        
    
  
Version history
Revision #:
1 of 1
Last update:
‎11-25-2015 09:37 AM
Updated by: