QlikView Your Business

An Expert Guide to Business Discovery with QlikView and Qlik Sense

Oleg Troyansky, Tammy Gibson, Charlie Leichtweis

• Publisher: Wiley
• Year: 2015
• ISBN:
  9781118949573
• Edition: 1
• Topic: Business
• Subtopic: Business intelligence
• Index: Business

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.

III
Expanding Your Skill Set: Profitability Analysis

• Chapter 7: Defining a Business Scenario for Profitability Analysis
• Chapter 8: Visualizing Profitability Analysis in QlikView
• Chapter 9: Data Modeling for Profitability Analysis
• Chapter 10: Developing a Data Load Script for Profitability Analysis

Chapter 7
Defining a Business Scenario for Profitability Analysis

Financial statements are widely used as the “report card” for business. Financial statements follow rules of accounting and are supposed to communicate to the reader a description of results in categories that drive a business. The income statement is a lens for analyzing the earnings of a business. This chapter will examine the income statement in accounting terms and how it falls short of providing critical analytical information to make decisions. You will be introduced to a more detailed analytical approach to understanding the profitability drivers of your business that we call the direct variable profitability (DVP) model. The example company information will illustrate data elements to consider when analyzing drivers of profitability and compare that visibility to what you see in the traditional accounting income statements.

The DVP Model will allow you to better understand the drivers of profitability of your business and prioritize resources to manage those drivers. This approach can be used in any business for any reporting period. Use of the DVP model is restricted only by the tools that you have available to access data in your systems.

The Profit and Loss Statement

It’s likely that you have seen what is commonly called a profit and loss statement (P&L) or an income statement. A P&L statement defines profit as being equal to revenue (sales), less all costs and expenses. Seems fairly straightforward; however, it is rarely obvious what action to take just by looking at this financial statement.

Consider a company that has the P&L statement shown in Table 7-1.

Table 7-1: Example company P&L statement (in thousands)

	Year 1	Year 2
Sales	$10,000	$12,000
Cost of Sales	$6,000	$7,200
Gross Profit	$4,000	$4,800
Percentage of Sales	40.0%	40.0%
Selling Expenses	$2,000	$2,500
Percentage of Sales	20.0%	20.8%
General & Administrative expenses	$1,000	$1,200
Percentage of Sales	10.0%	10.0%
Net Income/(Loss)	$1,000	$1,100
Percentage of Sales	10.0%	9.2%

Based on this statement, can you tell how the company is doing? And what it should do differently going forward? On the surface, the company seems to have grown sales by 20%, gross profit by 20%, and improved the profits by 10%. Seems like a winning accomplishment. But what does it really mean? For example, a company might grow its sales and profits in the first quarter of year 2 over the previous year and think they are successful. However, it could be that the company is unaware of or does not have visibility into a number of factors that influenced the sales dollars—factors that indicate a problem. Many factors affecting profitability (i.e., the relationship of expenses to sales) are critical to understanding your business and its potential. Those relationships can be defined by the following categories of cost and expenses:

• Variable costs—Variable costs vary directly with unit movement. Examples are material costs, volume discounts, la...