Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP

How to Create a Calculated Column in a PixelPerfect Report

cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_S
Support
Support

How to Create a Calculated Column in a PixelPerfect Report

Last Update:

Nov 19, 2015 6:35:33 PM

Updated By:

Frank_S

Created date:

Nov 19, 2015 6:35:33 PM

Description: This tutorial illustrates how to create calculated columns in a PixelPerfect report using Calculated Field

Add Calculated Field

Add-Calculated-Field.png

After adding CH341 - Sales by Product CH341 - Sales by Product in the Levels node:

  1. Right-click on CH341_Level
  2. Select Add Calculated Field from the menu
  3. Enter AvgPrice as Name
  4. Click on the browse button at the extreme right of the Expression field
Refer to How to Create a Simple PixelPerfect Report

Enter the Expression

Enter-the-Expression.png
  1. Enter [Sales] / [Quantity] as expression
  2. Click on OK
You can double click on a field to insert it in the expression

Insert the Detail Report Band for "CH341_Level"

Insert-the-Detail-Report-Band-for--CH341_Level-.png
  1. Right click on the Detail bar
  2. Select Insert Detail Report
  3. Select "CH341_Level". The result is DetailReport1 - "CH341_Level" bar.

Embed Headings in Template

Embed-Headings-in-Template.png
  1. Hold down the CTRL key on your keyboard while you select the following fields in the following order:ProductName, Sales, Quantity, AvgPrice
  2. Left click and hold on the selection made in Step 1.
  3. While holding the selection, drag and drop it into DetailReport - "CH341_Level" -> Detail1 band
  4. Drag the Detail1 bar upper border till it coincides with the lower border of the Level border to eliminate the white spaces

Format "CH341_Level" Cell

Format--CH341_Level--Cell.png
After clicking desired cell:
  1. Click on the small arrow above the upper right corner of the cell
  2. Click on the browse button at the extreme right of the Format String field
  3. Select Currency
  4. Click on $0.00 from the list. When done click on OK

Preview

Preview.png


Click on Preview

Result

Result.png

See the step below to create a more complex expression and avoid the Infinity issue

Edit the Expression

Edit-the-Expression.png

  1. Select AvgPrice expressions
  2. Click on the browse button at the extreme right of the Expression field
  3. Enter Iif([Quantity] != 0,[Sales] / [Quantity]  ,' - ' ) and click OK

Final Result

Final-Result.png

On previewing again, you can see that the word Infinity has been replaced with the ' - ' character where the quantity value is 0.

Version history
Last update:
‎2015-11-19 06:35 PM
Updated by: