QlikView Your Business
An Expert Guide to Business Discovery with QlikView and Qlik Sense
Published on 9. October 2015
Book
Paperback/Softback
800 pages
978-1-118-94955-9 (ISBN)
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 in the book 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.
More details
Persons
OLEG TROYANSKY is the President of Natural Synergies, the first North American BI firm that was created with the focus on QlikView solutions, services and education. He is a well-known QlikView educator and advocate, a 2014 QlikView luminary and a speaker at the Masters Summit for QlikView.
TAMMY GIBSON is a Senior QlikView Consultant at Natural Synergies. She's been developing QlikView solutions since 2004. She is also the co-founder and CTO of VenueCube, a company providing business intelligence analytics to entertainment venues and convention centers.
CHARLES LEICHTWEIS is a Senior Advisor of Phoenix Strategic Advisors and the head of their Sustainable Profitable Growth Practice. Charlie has led companies in a variety of "C" suite roles in a career that spans 30 plus years. He holds an MBA in Operations Management, and is a CPA and CMA.
Content
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
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