In this article by Karl Pover, author of the book Mastering QlikView Data Visualization, we can start to see how some set analysis and expressions are reused between different QlikView applications. As we create our first QV application for the marketing department, we’ll start to store the elements we in a centralized file so as to facilitate their reuse.
Marketing is a business area that combines data from multiple sources. Along with data from our Customer Relationship Management (CRM) software, we’ll take advantage of QVSource, a third-party tool that helps QlikView extract data from social networks like Facebook, Google+, LinkedIn and Twitter. If we have time we’ll also add data from market research companies, like Nielson, and public, open data sources, like data.gov.
(For more resources related to this topic, see here.)
Marketing Data Model
Let’s create a new QlikView application called Marketing.qvw with the 1.Application3.Marketing container of the QDF. Perform a binary load of the following QVW file.
Our primary source of information for our marketing department is our CRM. A CRM is how we keep track of our current and future customers. We not only store information about potential customers and track marketing campaigns, but also track sales activities and forecast along with customer service and support activities. Salesforce.com is a popular CRM and shares the market with SAP, Oracle and Microsoft who all provide CRM alongside their ERP software.
The marketing data model an extension of the sales data model. It contains the same dimensions (ie. SalesPersons, Products, Master Calendar and Customers) in addition to other dimensions that describe all the other events that occur as we study the market and our customers, approach potential customers and maintain current customers. Possible dimensions describe competitors, demographics, geography and types of activities.
The type of facts in the marketing data model in large mix of different events. Sales is one of those events as it is important to understand well your customers within the context of the whole market. Other events include social media campaigns, opportunities, annual market studies, visits, or telephone calls.
Even though we consider a large variety of events, we incorporate them into one fact table and identify the type of events in a field included in the same fact table.
Since we include sales into our QlikView marketing application it is important that we reuse the same expressions to calculate sales in the marketing application as we do the QlikView sales application. We do this by creating using variables and creating a library of variables for our competence center.
Variables in QlikView
The use of variables is a great way to reuse, facilitate and accelerate the development of multiple QlikView applications. They have multiple uses that include storing a fixed value (e.g. 203 or ‘string’), a whole expression or parts of an expression (e.g. set analysis or field names).
Remember to think lean. The use of variables is a better practice. If you will only ever create one QlikView application for your organization and the same expression is hardly used with it than variables will add a level of complexity that is not necessary.
The first and simplest use of variables is to store a fixed value that is either a number or a string value.
Remember that the QlikView Community is a great place to constantly learn about QlikView. Some of the content for this section, especially the names, is taken from Henric Cronström’s post about “The Magic of Variables” (http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables)
For example, we may store a variable to determine whether the user sees a certain sheet in QlikView.
- We can create a variable directly in the file menu under the Settings file menu by clicking on Variable Overview….
- In the Variable Overview window, click on Add and enter the variable name vShowSheetFlag.
- Select the new variable in the Variables list.
- In the Definition field, enter the value 1.
- Click on OK.
- Copy the sheet Main so that a new sheet called Copy of Main is created.
- In the sheet Copy of Main, open Sheet Properties.
- In Sheet Properties, select on the Conditional option and click on … to the right of the option.
- In the Edit Expression, click on the Variable tab.
- Select the vShowSheetFlag variable.
- Select the on the Paste button.
- Click OK twice and return to the sheet.
Nothing changes since the sheet will continue to appear when the vShowSheetFlag is assigned any number that is not 0. If we assign the value 0 to vShowSheetFlag then the sheet will disappear. Let’s give it a try.
- Open the Variables Overview window under the Settings file menu option and change the value of vShowSheetFlag to 0. Click on OK.
The sheet Copy of Main should no longer appear. It is now in hidden. In order to navigate to the hidden sheet we can change the value of the variables, but instead of changing the variable value in the Variable Overview window, we usually use the following methods are used to change the values of fixed value variables.
- Input Box
First, let’s create an input box and then later we’ll change the variable with an action.
- Create a new input box.
- In the New Input Box properties window select vShowSheetFlag
- Click on Add >
- Optionally, we can change the Label.
- In the Constraints tab, select the option Predefined Values Only.
- Uncheck the Enable Edit Expression Dialog checkbox
- Select the option Predefined Values in Drop-down
- Tick the Listed Values checkbox
- Enter the two possible values (0,1) separated be a semi-colon (;)
Back on the sheet, if we toggle the value in the new input box between 0 and 1, the sheet will disappear and appear, respectively.
Another way to toggle between disappear and appear is to use an action activated by a button.
- Create a new button object
- In the New Button Object properties window define the Text of the button to be the following expression so that when the sheet is hidden the button display Show Sheet, otherwise the button display Hide Sheet.
=if(vShowSheetFlag=0,'Show Sheet','Hide Sheet')
- In the Action tab, add the action to Set Variable, which is classified as an External Action Type.
- Enter vShowSheetFlag in the Variable field
- Enter following expression the Value field to toggle the variable between 0 and 1.
Back on the sheet, if we click on our new button the sheet will disappear and appear.
Static Variables can also be used for the following purposes.
- Show/Hide objects within a sheet like pop-up help windows, filters or charts.
- Establish user-defined object properties like the number of bars displayed in the bar chart or the ranges that define a metric is good or bad in a gauge. Remember that anything with a … button can be controlled by a variable or an expression.
Along with a fixed value variables, can also define dynamic variables with an expression.
When we create the QlikView marketing application is when we start to see how much we tend to reuse certain expressions, or parts of expressions. It is a great opportunity to start using variables more and even create a variable library within the QlikView Deployment Framework.
Since marketing data can come from many sources it is also a great opportunity to use QVSource, a third-party tool, to extract data from non-traditional sources. The QlikView marketing application is also challenges us to use data visualization best practices when the user may be accustomed to marketing designs that involves attracting a customer’s attention like the use of the color red or 3-D graphics. Avoid the temptation and stick with your guns as much as possible explaining by example.