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

Creating custom pop-up labels on a bar chart

The default pop up for a QlikView bar chart is useful but is not always exactly what you want.

The format is as follows:

Dimension name = value

Expression label = value

Now, we may not like this pop up and wish to display the values differently or add different formats. We may even want to include additional information.

In the preceding example, the pop up shows the value of Sales $ for Germany. If I want to see the value of the Costs $, I need to hover over the Costs $ bar. And what if I wanted to see Margin $ or Margin %?

Getting ready

Create a new QlikView document and save it to a folder. Edit the script (Ctrl + E or the File menu – Edit Script).

Enter the following script:

LOAD * INLINE [
    Country, Sales, Costs
    USA, 1000, 800
    UK, 800, 700
    Germany, 900, 1000
    Japan, 600, 400
];

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

How to do it…

Use the following steps to create a new bar chart and add custom labels:

  1. Create a new bar chart with the following characteristics:

    Dimension

    Country

    Expression 1

    Sum(Sales)

    Expression 2

    Sum(Costs)

  2. Click on Finish.

  3. You should see a bar chart with two bars for each country. Confirm that the pop up on each bar displays as expected.

  4. Open the chart properties.

  5. Click on the Presentation tab and deselect the Pop-up Labels checkbox. Click on OK and note that there are no longer any pop-up labels appearing.

  6. Edit the properties again and click on the Expressions tab. Click on the Add… button and enter the following expression:

    ='Sales : ' & Num(Sum(Sales), '#,##0')

  7. Click on OK.

  8. Deselect the Bar option for this expression and turn on the Text as Pop-up option. Click on OK.

    Note

    Note that the new custom pop up is now active and displays the sales value whether you hover over the Sales $ or Costs $ bar.

  9. Edit the properties again and edit the pop-up expression as follows:

    = Country & chr(10)  
& 'Sales : ' & Num(Sum(Sales), '$(MoneyFormat)') & chr(10)
& 'Costs : ' & Num(Sum(Costs), '$(MoneyFormat)') & chr(10)
& 'Margin : ' & Num(Sum(Sales)-Sum(Costs), '$(MoneyFormat)') 
& chr(10) & 'Margin % : ' 
& Num(1-(Sum(Costs)/Sum(Sales)), '0.0%')

  10. Click on OK on the expression editor and then click on OK to close the properties.

  11. Check that the custom pop up is displayed.

How it works…

By turning off the Bar option for the expression, QlikView will not try and evaluate the expression as a value and will not try to render a bar for it. By turning on the Text as Pop-up option, we tell QlikView to calculate the text of the expression and display it in the pop up.

We also had to turn off the default pop-up option in the Presentation tab or else it would display both (which might be what you want, have a play with it).

The chr function is useful to know about adding, so called, non-printable characters into your output. chr(10) is a line feed that moves the text onto the next line.

Note

Note that we have used a variable here for the format string. MoneyFormat is one of the standard QlikView variables that are generated in the script when you first open the script editor.

There's more…

The cool thing about this is that you can use it everywhere! It works in a lot of different charts and is a great way of giving users additional information about the data that they are looking at.

Previous Section
End of Section 3
Next Section