QlikView Your Business

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