Skip to main content

QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense



QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Oleg Troyansky, Tammy Gibson, Charlie Leichtweis, Lars Bjork (Foreword by)

ISBN: 978-1-118-94957-3 July 2015 800 Pages

Download Product Flyer

Download Product Flyer

Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description.


Unlock the meaning of your data with QlikView

The Qlik platform was designed to provide a fast and easy data analytics tool, and QlikView Your Business is your detailed, full-color, step-by-step guide to understanding Qlikview's powerful features and techniques so you can quickly start unlocking your data’s potential. This expert author team brings real-world insight together with practical business analytics, so you can approach, explore, and solve business intelligence problems using the robust Qlik toolset and clearly communicate your results to stakeholders using powerful visualization features in QlikView and Qlik Sense.

This book starts at the basic level and dives deep into the most advanced QlikView techniques, delivering tangible value and knowledge to new users and experienced developers alike. As an added benefit, every topic presented is enhanced with tips, tricks, and insightful recommendations that the authors accumulated through years of developing QlikView analytics.

This is the book for you:

  • If you are a developer whose job is to load transactional data into Qlik BI environment, and who needs to understand both the basics and the most advanced techniques of Qlik data modelling and scripting
  • If you are a data analyst whose job is to develop actionable and insightful QlikView visualizations to share within your organization
  • If you are a project manager or business person, who wants to get a better understanding of the Qlik Business Intelligence platform and its capabilities
What You Will Learn:

The book covers three common business scenarios - Sales, Profitability, and Inventory Analysis. Each scenario contains four chapters, covering the four main disciplines of business analytics: Business Case, Data Modeling, Scripting, and Visualizations

The material is organized by increasing levels of complexity. Following our comprehensive tutorial, you will learn simple and advanced QlikView and Qlik Sense concepts, including the following:

Data Modeling:

  • Transforming Transactional data into Dimensional models
  • Building a Star Schema
  • Linking multiple fact tables using Link Tables
  • Combing multiple tables into a single fact able using Concatenated Fact models
  • Managing slowly changing dimensions
  • Advanced date handling, using the As of Date table
  • Calculating running balances
Basic and Advanced Scripting:
  • How to use the Data Load Script language for implementing data modeling techniques
  • How to build and use the QVD data layer
  • Building a multi-tier data architectures
  • Using variables, loops, subroutines, and other script control statements
  • Advanced scripting techniques for a variety of ETL solutions
Building Insightful Visualizations in QlikView:
  • Introduction into QlikView sheet objects — List Boxes, Text Objects, Charts, and more
  • Designing insightful Dashboards in QlikView
  • Using advanced calculation techniques, such as Set Analysis and Advanced Aggregation
  • Using variables for What-If Analysis, as well as using variables for storing calculations, colors, and selection filters
  • Advanced visualization techniques - normalized and non-normalized Mekko charts, Waterfall charts, Whale Tail charts, and more

Building Insightful Visualizations in Qlik Sense:

  • Introducing Qlik Sense - how it is different from QlikView and what is similar?
  • Creating Sense sheet objects
  • Building and using the Library of Master Items
  • Exploring Qlik Sense unique features — Storytelling, Geo Mapping, and using Extensions

Whether you are just starting out with QlikView or are ready to dive deeper, QlikView Your Business is your comprehensive guide to sharpening your QlikView skills and unleashing the power of QlikView in your organization.

Foreword xiii

Introduction xv

PART I Getting Started

Chapter 1 The Needs and Challenges of Business Intelligence and Analytics 3

The Case for Business Intelligence 4

Common Challenges of Business Analytics 6

How Successful Businesses Use Business Intelligence 7

Introducing the Six Process Spheres 7

Identifying Business Measures 10

What Companies Gain from Implementing BI 14

The Business Scenario Used in the Book 16

Chapter 2 Why Use Qlik for Data Discovery and Analytics? 17

The Evolution of BI 18

Traditional Business Intelligence (OLAP) 18

Qlik’s Disruptive Approach to BI 20

Data Discovery Is the New Black 22

QlikView 11 Overview 23

In-Memory Storage Means No Need for Pre-Calculated Cubes 23

An Interactive User Experience 24

Associative Logic Powers Data Discovery 26

Right-Sized Analytics 32

Qlik Sense Overview 32

PART II Learning the Core Techniques: Sales Analysis

Chapter 3 Defining a Business Scenario for Sales Analysis 41

“What Do You Mean When You Say Sales?” 42

What Is the Real Value of the Sale? 43

What Happened? 45

Why Did it Happen? What Does It Mean for

My Business? 46

What Data Is Needed 50

Advanced Sales Analysis Makes the Data Visible and Available 51

Chapter 4 Visualizing Sales Analysis in QlikView 53

Preparing the Environment and Getting Ready 54

Opening a Template Document 54

Sheets and Sheet Objects 57

Working with Colors in QlikView 59

List Boxes, Text Objects, and

Other Sheet Objects 60

Introducing List Boxes and Multi Boxes 61

Table Box and How It Should (Not) Be Used 65

Using Text Objects for Labels, Images, and Backgrounds 66

Line/Arrow Object 69

Mind Your Selections: Search Object and Current Selections Box 70

Organizing and Arranging Objects on the Screen 73

Developing Simple Charts in QlikView 78

Main Components of QlikView Charts 79

Using Bar Charts to Compare Outcomes Among Brands,

Channels, and Salespeople 82

Using Line Charts to Visualize Trends over Time 92

Using Pie Charts to Visualize Distribution of the Whole

Between its Parts 97

Using Straight Tables and Pivot Tables to Show Details 100

First Round of Improvements 111

Gathering Feedback at the First Application Review 111

Using Groups to Add Flexibility to Your Charts 113

Comparing YTD to Prior YTD or Other Conditions 118

Building Dashboards 129

Bringing It All Together 140

Chapter 5 Data Modeling for Sales Analysis 145

Data Modeling Basics 146

The Transactional Software Class 147

Using ETL to Populate Dimensional Tables 155

Designing a Data Model for Sales Analysis 159

Tips Before You Start Scripting 168

Chapter 6 Developing a Data Load Script for Sales Analysis 169

Load Script and Script Editor 170

Script Editor Basics in QlikView 170

Anatomy of a QlikView SQL Select Statement 177

Organizing Your Script 179

Using the Table Viewer and Understanding the Data Model 186

Resolving Simple Challenges in Data Load 188

Introducing Synthetic Keys and Circular References 189

Avoiding Synthetic Keys by Renaming Fields 191

Using the QUALIFY and UNQUALIFY Commands 192

A Few Words About Naming Conventions 196

Transforming the Data Model 197

Loading Data from a Spreadsheet 198

Enhancing the Data Model for Online Analytics 204

Joining Two Tables into One 207

Adding Calculated Fields 213

Additional Transformations: Calculating Conditional Flags 222

Adding a Master Calendar Table Using INCLUDE 233

PART III Expanding Your Skill Set: Profitability Analysis

Chapter 7 Defining a Business Scenario for Profitability Analysis 241

The Profit and Loss Statement 242

The Direct Variable Profitability (DVP) Model 245

Deep Dive Example 248

Use of Business Intelligence Tools Makes the Data Visible and Available 251

Chapter 8 Visualizing Profitability Analysis in QlikView 253

Simple Visualizations for Profitability Analysis 254

Preparing the Template for Profitability Analysis 254

Preparing the Expressions for Profitability Calculations 256

Revisiting Simple Visualization Objects 286

Learning Advanced Visualizations 292

Visualizing Components of Costs and Profits 292

How to Visualize Correlations Between Sales and Profits 308

Advanced Visualizations Using Set Analysis 339

Using Containers as an Alternative to Auto-Minimized Charts 349

Chapter 9 Data Modeling for Profitability Analysis 355

How QlikView Stores Data 356

Modeling Multiple Transaction Sources 361

Multiple Fact Tables 362

The Concatenated Fact Table 369

Working With Dimensions 370

Slowly Changing Dimensions Defined 371

Product Costs as Dimension Attributes 373

Product Costs as Fact Metrics 375

Chapter 10 Developing a Data Load Script for Profitability Analysis 379

Creating a QVD Data Layer 380

Why Do You Need a Separate Data Layer? 380

Introducing the QVD File Format 381

Variables and Script Control Statements 384

Troubleshooting QlikView Load Scripts 406

Creating Transformed QVD Files for Profitability Data 411

Generating New Transformed QVDs 415

Building the Data Model for Profitability Analysis 416

An Overview of the Process 417

Aggregating Data in QlikView 418

How to Use Mapping in QlikView Script 420

Using Aggregation and Mapping to Load the Credit Memos 425

Building the Link Table 431

Building the Master Calendar 439

A Few Words About Link Tables 452

A Round of Improvements 455

Modifying the Data Model to Restore Missing Associations 456

Loading Complex Spreadsheets 457

Adding Commissions Data to the Data Model 470

Explaining the Multi-Tier Data Architecture 471

PART IV Mastering Advanced Techniques: Inventory Analysis

Chapter 11 Defining a Business Scenario for Inventory Analysis 477

What Is Inventory—Asset or Liability? 477

Factors to Consider When Measuring Inventory 479

The Definition of Inventory Analysis 481

Commonly Used Metrics for Inventory Analysis 482

Common Data Elements Required for Inventory Analysis 484

The Benefits of Advanced Inventory Analysis 485

Chapter 12 Visualizing Inventory Analysis in QlikView 489

Developing the Key Measures for Inventory Analysis 490

Preparing the Environment for Inventory Analysis 490

Using Advanced Aggregation (AGGR) for

Inventory Measures 500

Enhancing Visual Analysis with Advanced Aggregation 513

Developing Histograms 513

Visualizing Parts of Totals and Subtotals 518

Bucket Analysis 536

Does It Get Any More Advanced with Advanced Aggregation and Advanced Set Analysis? 542

Comparing TOTAL with AGGR() 542

Using TOTAL and AGGR() with Dimensions Outside of the Chart Data 545

More About Advanced Set Analysis 548

Comparative Analysis Using Alternate States 556

Declaring Alternate States 556

Assigning Objects to Alternate States 557

Using Alternate States in Combination with Set Analysis 560

Combining Selections from Multiple States 561

Getting Advanced with Straight Tables 563

Using Gauges in Straight Tables 564

Mini Charts (Sparklines) 567

Adding Images to Straight Tables 570

Adding Links (URLs) to Straight Tables 574

Advanced Presentation Features—Buttons, Actions, and Triggers 577

Buttons and Actions 578

Using Show Conditions and Calculation Conditions 582

Using Buttons, Actions, and Show Conditions to Develop Advanced

Navigation Features 583

Chapter 13 Data Modeling for Inventory Analysis 589

Calculating Running Balances 590

The Logic of Calculating a Running Balance 590

How Running Balances Apply to Inventory and Inventory Aging 591

The Concatenated Fact Table 593

Introducing the Concatenated Fact Table 593

Resolve Missing Associations 596

Forcing Alignment with Link Tables and Generic Keys 598

Link Tables or Concatenated Facts? 600

Advanced Date Handling 601

Should Date Flags Reside in the Master Calendar or in the Fact Table? 601

Supporting Flexible “As Of” Dates 604

Chapter 14 Developing a Data Load Script for Inventory Analysis 607

Review of the Business Requirements and the Data Sources 608

Transforming Inventory Data 610

The Process of Generating the Aging Data 610

Transforming Sales Data for Inventory Analysis 616

Transforming On-Hand Balances and On-Hand History Data 626

Storing the Transformed QVD Files 627

Building the Concatenated Fact Table 628

Combining Multiple Facts in a Single Table 628

Propagating Missing Attributes to All the Slices of the Concatenated Fact 632

Adding Dimension Tables and Calculated Fields 635

Restoring Missing Associations 640

Building the As Of Date Table 648

Generating Chart Expressions in the Script 654

Creating Expressions Stored in Variables in the Script 655

Loading Expressions from External Text Files 660

A Few Words in Conclusion 667

Presentation Features Not Covered in the Book 668

Scripting Features Not Covered in the Book 669

PART V Qlik Sense

Chapter 15 Loading Data in Qlik Sense 673

Getting Oriented with Qlik Sense 674

Introducing the Desktop Hub 674

Create a New App 676

Using Quick Data Load 678

The QDL Wizard 678

Drag and Drop 679

Using the Data Load Editor 681

Navigation and Layout 682

Introducing Data Connections 683

Loading Data from QlikView into Qlik Sense 689

Loading Data for Sales Analysis 690

Using Developer Tools 695

Data Model Viewer 695

Debug Panel 698

Chapter 16 Developing Visualizations in Qlik Sense 701

Exploring the Qlik Sense Visual Environment 702

Navigation in Qlik Sense 702

Building Simple Charts 703

Introducing the Library of Master Items 707

Developing the Sales Analysis Dashboard in Qlik Sense 713

Building More Advanced Analytics in Qlik Sense 721

Selections in Qlik Sense 728

Making Selections in Charts 728

Working with Current Selections 729

The Search and Selections Tools 730

Introducing the Filter Pane 732

Storytelling 733

Taking Snapshots 733

Creating Stories 735

Using Story Playback 737

Geo-Mapping in Qlik Sense 738

Loading Geography Data 738

Creating Maps 743

Integrating Qlik Sense Apps 749

Defining Qlik Sense Integration Terminology 749

Integrating Extensions in Qlik Sense 750

Exploring the Feature of Converting Charts 752

Where to Find More Extensions and More Information 753

What About Mashups? 754

Appendix What’s Next? 755

Index 759

Qlikview Your Business file
Click to Download