Excel reports with nested levels and subtotals

You can nest levels to create a hierarchy and use Excel formulas to make calculations.

For example, you can create a year > category hierarchy to obtain a report with sales for each product category for each year. You can add summary formulas and labels to each level of the report to show which values are displayed in that level. To learn about levels, see: Excel reports with levels.

What you will do

In this tutorial, the QlikView objects inserted between the level opening and closing tags will be sub-divided in the final report.

You will:

1. Create a custom table by adding two table columns.
2. Add two fields as levels so that the Excel report presents three tiers of information.
3. Add SUM formulas so your tables have totals and subtotals.
4. Customize SUM formulas using Excel formatting. This tutorial has suggested formatting, but you can customize your design to your specifications.

This tutorial uses QlikView data that can be found in Sample files. You can also use your own QlikView or Qlik Sense data.

Creating a new Excel report

Do the following:

1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
2. Enter a Title for the report. Report with nested levels and subtotals.
3. Select Excel from the Type drop-down list.
4. Select an app from the App drop-down list.
5. Click Create to create the report.
6. Click Edit template to open the Template Editor.

Choosing levels and table objects

1. Right-click the Levels node, and select Add levels.

2. Click the objects that you want to add. For example, add the Country and CategoryName fields.

   You can hold down CTRL or Shift to select multiple items.

3. Click OK.

   

4. Right-click the Tables node, and select Add objects.
5. Click the object that you want to add. For example, select Top 10 Products.

6. Click OK.

   

Adding the table

You can add the entire table object to the template. In this example, you will only add two columns.

Do the following:

1. Under the Tables node, expand the Top 10 Products node.
2. Click on Total Sales. In the Properties pane, clear Keep Source Formats.
3. Repeat for ProductName.

4. Drag the ProductName and Total Sales nodes onto empty cells in the template.

   For example, cells D6 and E6.

5. Click on <Total Sales>, and format as currency.

6. In cell E8, enter the Excel formula: =SUM(E6:E7).

   This formula includes an empty row, so Qlik NPrinting will add rows as necessary to contain all values.

7. In cell D8, type Total:.

8. Format cell E8 using Excel formatting.

   For example:

   • 12px bold font
   • Right justified alignment
   • Custom = Accounting with no digits to the right of the decimal point.

   

Adding the first level

Do the following:

1. Drag the Country_Level node onto cell C4.
2. Move the </Country_Level> closing tag down to C10, so that Country Level includes the cell containing the SUM, and an empty row.
3. Click + to expand the Country_Level node.

4. Drag the Country node token onto cell C6.

   This adds a dynamic title.

5. Format cell C6 to 12px and bold.

6. In cell E11, enter the formula: =SUM(E6:E11)/2.

   The sum is divided by two because the SUM function will include all values, including the sum of those values that are in cell E8.

7. Format cell E11 to:
   • 12px bold font
   • Right justified alignment
   • Custom = Accounting with no digits to the right of the decimal point.

   

Adding the second level

Do the following:

1. Drag the CategoryName_Level node token onto cell A2.
2. Drag the </CategoryName_Level> closing tag down to A14.
3. Click + to expand the CategoryName_Level node.

4. Drag the CategoryName node token onto cell B3.

   This adds a dynamic title.

5. Format cell B3 to 16px bold font.
6. Drag a second CategoryName node to cell D11. Double-click this cell and add the word "total", so the cell displays <CategoryName> total:.
7. In cell D14, type in Grand total:.

8. In cell E14, enter the formula: =SUM(E2:E14)/3

   The sum is divided by three because the SUM function will add all the values, including the subtotals in cells E8 and E11.

9. Format cell E14 to:
• 14px bold font
• Right justified alignment
• Custom = Accounting with no digits to the right of the decimal point.

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will have a report organized by your first level, and then your second level.

   

3. Each table will have a total underneath. Each category will have a total with a dynamic label.

   

4. At the bottom, there will be a grand total of all products from all countries.

   

5. Click Save and Close to save the template, and close the Template Editor.