Book Image

QlikView for Developers Cookbook

By : Stephen Redmond
Book Image

QlikView for Developers Cookbook

By: Stephen Redmond

Overview of this book

QlikView has been around since 1993, but has only really taken off in recent years as a leader in the in-memory BI space and, more recently, in the data discovery area. QlikView features the ability to consolidate relevant data from multiple sources into a single application, as well as an associative data model to allow you to explore the data to a way your brain works, state-of-the-art visualizations, dashboard, analysis and reports, and mobile data access. QlikView for Developers Cookbook builds on your initial training and experiences with QlikView to help you become a better developer. This book features plenty of hands-on examples of many challenging functions. Assuming a basic understanding of QlikView development, this book provides a range of step-by-step exercises to teach you different subjects to help build your QlikView developer expertise. From advanced charting and layout to set analysis; from advanced aggregations through to scripting, performance, and security, this book will cover all the areas that you need to know about. The recipes in this book will give you a lot of the information that you need to become an excellent QlikView developer.

Related Content you might be interested in

Current Title:

Small book image
QlikView for Developers Cookbook
Stephen Redmond
Jun, 2013 | 290 pages
No titles found
Table of Contents (19 chapters)
QlikView for Developers Cookbook
QlikView for Developers Cookbook
Credits
Credits
Foreword
Foreword
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Charts
Charts
Introduction
Creating custom pop-up labels on a bar chart
Creating a box plot chart for a simple data set
Using the wizard to create a box plot chart
Creating a "Stephen Few" bullet chart
Creating a modified bullet chart in a straight table
Creating a bar chart in a straight table
Creating a Redmond Aged Debt Profile chart
Creating a waterfall chart
Replacing the legend in a line chart with labels on each line
Creating a secondary dimension in a bar chart
Creating a line chart with variable width lines
Brushing parallel coordinates
Using redundant encoding with a scatter chart
Staggering labels in a pie chart
Creating dynamic ad hoc analysis in QlikView
2
Layout
Layout
Introduction
Changing the default object layout options
Changing the default selection color scheme
Modifying the green, white, and gray selection color schemes
Modifying the green, white, and gray selection color schemes in QlikView Server
Using containers as an alternative to multiboxes
Using the design menus to custom format a cell
3
Set Analysis
Set Analysis
Introduction
Using dollar expansion in Set Analysis to enable from-date and to-date selection
Using alternate states with Set Analysis
Using Set operators to exclude values from results
Using Set Analysis with a Date Island
Using Sets to avoid key tables
4
Advanced Aggregations
Advanced Aggregations
Introduction
Using TOTAL to calculate the percentage of total and subtotal
Using AGGR to calculate the percentage of the maximum value
Using AGGR to resolve a "Sum of Rows" issue
Creating a dynamic AGGR expression for a Group dimension using Dollar Expansion
Using RangeMax to return only positive numbers
Creating a dynamic Part-to-Whole pie chart
Creating a colored treemap using colormix
Using RangeSum to calculate a rolling total in a multidimension table
Showing only the top 3 values in a pivot table
Creating a Statistical Control Chart using Standard Deviation
Creating a Moving Range chart
Creating a control chart using Moving Range
5
Advanced Coding
Advanced Coding
Introduction
Extracting QlikView data to a Word report
Printing reports to PDF using PDFCreator
Creating a chart using a macro
Using VBS functions in charts
6
Data Modeling
Data Modeling
Introduction
Concatenation of fact tables to avoid loops and synthetic keys
Creating a Key/Link table in QlikView
7
Extensions
Extensions
Introduction
Creating a simple HTML extension
Creating a simple HTML table
Creating an interactive extension
Using external libraries with extensions
8
Useful Functions
Useful Functions
Introduction
Handling null in numeric fields or calculations
Using Dual to handle period name sorting
Parsing text to numbers and dates
Calculating Year To Date dynamically
Labeling a pie chart to replace the legend
Calculating the lowest or highest value in a range
Consolidating a date-time value into quarter hourly segments
Dynamically filtering by From/To dates
9
Script
Script
Introduction
Creating flags in the script
Replacing IsNull
Storing and dropping using a subroutine
Keeping a trace on things
Using the AND mode in listboxes
Using Exists and Keep to limit the data load
Setting the default display format
Setting the default sort order
Matching financial periods to dates
Handling partial reload in the script
Using Peek and Previous to calculate against loaded records
Creating a simple Gantt for a dashboard using Interval Match
Reading users from Active Directory
Getting a sub-URL using the Table wizard
Using parameters in Dollar Sign Expansion
Removing fields with a wildcard
Handling multiple subfolders in a script
10
Improving Performance
Improving Performance
Introduction
Reducing the number of distinct values
Creating counter fields to avoid Count Distinct
Creating flag fields to avoid Sum of If and other inefficient expressions
Denormalizing for performance
11
Security
Security
Introduction
Section Access gotchas
Blocking user access to a field using OMIT
Making all values available to Admins and Managers
Index
Index

Using AGGR to calculate the percentage of the maximum value

AGGR is the function that we will use to perform vertical calculations.

In this case, we are going to work out which country has the highest value, and calculate the percentage of other sales versus that highest value.

Getting ready

Load the following script:

LOAD * INLINE [
  Country, City, Sales
  USA, San Diego, 24567
  USA, Dallas, 54962
  USA, New York, 67013
  USA, Boston, 45824
  UK, London, 64002
  UK, Birmingham, 44291
  UK, Manchester, 40320
  Germany, Berlin, 52912
  Germany, Frankfurt, 61832
  Germany, Munich, 35812
  Japan, Tokyo, 42137
  Japan, Yokohama, 55832
  Japan, Osaka, 37643
];

How to do it...

Use the following steps to use AGGR to calculate the percentage of the maximum:

  1. Create a new straight table with Country and City as dimensions.

  2. Add the following expressions:

    Sales $

    Sum(Sales)

    % of Max

    Sum(Sales) /

    Max(TOTAL Aggr(Sum(Sales), Country, City))

  3. On the Expression tab, for the second expression, under...

Previous Section
End of Section 4
Next Section