QlikView for Developers Cookbook

QlikView for Developers Cookbook

By : Stephen Redmond

Buy this Book

QlikView for Developers Cookbook

By: Stephen Redmond

Buy this Book

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.

QlikView for Developers Cookbook

Credits

Foreword

About the Author

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

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

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

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

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

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

Data Modeling

Introduction

Concatenation of fact tables to avoid loops and synthetic keys

Creating a Key/Link table in QlikView

Extensions

Introduction

Creating a simple HTML extension

Creating a simple HTML table

Creating an interactive extension

Using external libraries with extensions

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

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

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

Security

Introduction

Section Access gotchas

Blocking user access to a field using OMIT

Making all values available to Admins and Managers

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Creating a box plot chart for a simple data set

Box plot charts, or box and whisker charts, are often used for displaying statistical mean and quartile information.

This can be useful for seeing how a value ranges across categories by visualizing the median, the twenty-fifth and seventy-fifth percentile, and the outlying values.

With a simple data set, it is easier to create the chart manually than using the wizard that QlikView provides—although there is a slightly strange sequence of actions to go through a "funny"—that you need to know about.

Getting ready

Load the following script:

LOAD * INLINE [
    Country, Value
    USA, 12
    USA, 14.5
    USA, 6.6
    USA, 4.5
    USA, 7.8
    USA, 9.4
    UK, 11.3
    UK, 10.1
    UK, 3.2
    UK, 5.6
    UK, 3.9
    UK, 6.9
];

How to do it…

There is a "funny" in here because you have to tell the chart properties that you are using a box plot, then click on OK and then go back in and edit the properties. Here's how you do it:

Create a new combo chart. Select Country as the dimension.
When the expression editor pops up, just enter 0 as the expression. It doesn't really matter what you enter, you just need to enter something.
Deselect Bar and select Box Plot. At this stage, just click on Finish. Your chart will come up with No Data to Display—this is not a problem.

When you go back into editing the expression properties, you will find five new subexpressions. Enter the following expressions for each of them:

Box Plot Top	`Fractile(Value, .75)`
Box Plot Bottom	`Fractile(Value, .25)`
Box Plot Middle	`Fractile(Value, .5)`
Box Plot Upper Whisker	`Max(Value)`
Box Plot Lower Whisker	`Min(Value)`

Add two new expressions:
```
Max(Value)
Min(Value)
```
For the two new expressions, deselect the Line option (or Bar if it is selected) and select the Symbol option. Select Circles from the Symbol drop-down menu:
On the Presentation tab, turn off Show Legend. Set the Symbol Size option to 3 pt:
Check that the box plot looks like the following screenshot:

How it works…

The five separate values define the different portions of the box plot:

Top	The top of the box
Bottom	The bottom of the box
Middle	The median value, the line in the box
Upper Whisker	The upper outlier value
Lower Whisker	The lower outlier value

There's more…

This recipe is for when you have a simple set of data and you are looking for the statistics across all of the data.

In QlikView, we can have much more control of the values in the box plot, especially where we want to look at averages and percentiles of aggregated data.

As well as box plots, within the combo chart settings there is also a Stock option, which allows us to specify the minimum, the maximum as well as an open and close value.

QlikView for Developers Cookbook

By : Stephen Redmond

QlikView for Developers Cookbook

By: Stephen Redmond

Overview of this book

Related Content you might be interested in

Current Title:

QlikView for Developers Cookbook

Creating a box plot chart for a simple data set

Getting ready

How to do it…

How it works…

There's more…

See also