Category Archives: Productivity

What is BI Self Service?

A lot of the buzz around Qlik Sense is “Self Service” capabilities. Some people assume from that buzz that QlikView is  not so good with Self Service. But what is BI “Self Service” anyways?

The goal of BI is to provide answers and insights to the business. Those answers come from interacting with content, not creating content.  A reasonably well designed QlikView application used by someone with a small amount of training has always provided great Self Service.  That is, if you define Self Service as a business user being able to quickly answer new  business questions without engaging a developer.

Let’s look at an  scenario. The Canadian government has proposed a 10% tariff on the very class of goods you sell.  What might this tariff mean to your company? Quick — how much do you sell to Canada? If you pass this increase directly to the customer, how many customers do you need to talk to?

Do you require a chart of “Sales by Country” to answer this question? If you can select Country=Canada somewhere in the app, can you use the Total line from the “Sales by Year” chart to answer the question?

What if there is no Country listbox? Do you need to create one? Or can  you use associative search, a search object, advanced search or a Country dropdown in a chart to make the selection?

QlikView can answer a broad range of questions with just a few charts and listboxes.

I’m not knocking Qlik Sense. The same excellent Self Service capabilities are available there with the added bonus that content creation, when required, is much easier. Plus you get consistent implementaion of current selections and global search.

I  do think that an opportunity is overlooked if we first imagine Self Service as content creation rather than interaction.   I enjoy teaching the two day QV Designer class to business users.   However, I think many  of the students may be better served by a two hour class that focuses on how to navigate QlikView well.

I don’t think of a “Power User” as someone who creates charts but rather someone who knows how to search, select and  associate.   A Power User doesn’t write a Set Analysis expression. They make a series of selections and store it in a bookmark.

What comes to your mind when you think “Self Service BI”?

-Rob

 

Share

Using SR11 “SilentErrorInChart” Switch

Last month I wrote about error handling changes in Qlikview SR11. Today I want to relate my experience in using the new “SilentErrorInChart” switch during development.

Let’s start with an excerpt from the SR11 Release Notes:

As a result of fixing bug 69228 “Syntax checker not working”, syntax error messages in object expressions are now returned to objects instead of returned as NULL. This may result in an object which rendered in SR10 or earlier, displaying an error message in SR11 (”Error in expression”).

This feature changes what you see — if a chart column has an error. Prior to QV11,  the chart would render, but values in the error column would display a null (“-“):

With SR11, the default behavior is to not render the chart and instead display a chart level error message:

This is useful. It clearly raises the flag that one of this chart’s expressions is returning an error. But which expression? You will need to go through the expressions one by one, and if there is more than one column in error, you will repeat the exercise.

Can I return to the pre-SR11 behavior? Where the chart renders what columns it can, so I can identify what columns are returning errors? Yes! There is a setting for that.

The setting is not (yet) configurable through the User Preferences Dialog. So we need to use the backdoor “easter egg” to modify the setting. To reach the easter egg settings dialog, select “Help. About QlikView”  and then Right-Click on the QV bullseye logo in the lower left of the dialog box.

 

In the Settings list, scroll down and select (left click) “SilentErrorInChart” to display or modify the current setting.

A Value of “0” for this setting means to use the new SR11 behavior. That is, any column in error will cause the entire chart to not render.

A Value of “1” will return to the pre-SR11 behavior. QV will render what columns it can, and display “-” for those columns with errors.

To modify the setting, overtype the Value, press the Set button followed by the Close button. There is no need to exit QV to recognize a change for this setting. However, the chart must be “re-rendered” to utilize the setting. Easiest method I have found is to edit and save (“OK”) the chart Properties.

I’m finding that during most development, I set “SilentErrorInChart” to “1”. I want to identify columns in error as I create them.

In my final pre-production check, I’m finding that changing “SilentErrorInChart” to “0” is a useful quality control check to dramatically surface any chart expression problems.

-Rob

Share

Qlikview Components (QVC) Training June 4

I’ll be leading a 3.5  hour training June 4 on using the free open source “QlikView Components” scripting library.   The training is held on-line and you can find out more and register at this link on the Qlik-On website.

QV Developers around the world use QVC in their scripting to:

  • Save time.
  • Improve Quality.
  • Implement Advanced Functions.
  • Get it Right the First Time.

Create a master calendar? No problem — one line of script:

CALL Qvc.CalendarFromField('OrderDate');

Fiscal calendar that begins on Month 4? Again no problem:

CALL Qvc.CalendarFromField('OrderDate','','',4);

What was that Set Analysis syntax for month-to-date in the previous year? The Calendar generates a series of variables for common period to period analysis.

=Sum($(vSetPreviousYearMTD) Sales)

That’s all there is to it. Even if you add fields to the Calendar. Even if you use Italian or Norwegian for the calendar field names.

Quick! Write the script to create an AsOf table for field Year-Month.  I’m waiting…   I can’t do it either without rooting around for an old project to copy from. But I can write:

CALL Qvc.AsOfTable('Year-Month');

Here’s are a few more things QVC can do:

  • Load variables from an external file.
  • Load custom icons from a folder.
  • Logging, including rolling external files.
  • Pause mid-script and let you inspect the contents of a table.
  • Incremental Reloads!
  • NVL!

The QVC project was founded in 2012 with the goal of simplifying common scripting tasks and implementing best practices.  The focus is on what we call the “big middle” of scripting tasks. 

The library is continually refined and enhanced with input from QV developers around the world and we expect some exciting new testing routines soon!

I hope you can join me June 4 to learn the details of installing and using QVC. You will leave the class with the confidence and skills to use all the QVC routines and begin supercharging your script!

-Rob

 

 

 

Share

Name That QVW

Recommendation: Give all your QVW files unique names.  Do not, for example,  maintain both;

Sales\Dashboard.qvw
Finance\Dashboard.qvw

Instead do:

Sales\SalesDashboard.qvw
Finance\FinanceDashboard.qvw

If you want to stop here at TLDR (Too Long Didn’t Read) just take the recommendation. Use unique names. For the geek crowd, read on,

The reason for preferring globally unique names is metadata.  QlikView provides a number of different tools and facilities for scanning QVWs that provide useful (metadata) information for managing your pool of QV documents. Among them are:

– The lineage elements in a QVD that tell you what QVW created this QVD.

– QvsAdminDataProvider.dll that exposes information on user created server objects and ownership.

-QvServerObjectConnector.exe that exposes information on the Dimensions and Measures used in those server objects.

– A scanner you may write yourself (or one of my tools) that reads information from QVD or QVW files.

Each of those providers may use a different full path representation for the QVW file.  These differences result in a different  “identity” for the QVW and create difficulties in coalescing these very useful bits of information together to the common QVW file.

The lineage elements record the path at reload time, which is probably the server path, which does not align with the mapped drive you use in your scanner app.

QvsAdminDataProvider uses Access Point paths, which are URL paths like:
Dist/Sales/Dashboard.qvw

QvServerObjectConnector uses physical paths to the .shared file which are like:
X:\qlikvewserver\DistributedDocuments\Sales\Dashboard.qvw

These different path points of view make it difficult to tie the information together. If we can assume that the qvw name “Dashboard.qvw” is an identifying key, there is much insight available by combining these various metadata sources,

Bottom line. Give your QVW files globally unique name to get the most leverage of the metadata.

-Rob

There are still seats available for the San Francisco Master Summit for QlikView. Join us to learn about Advanced Scripting with me, Visualization Techniques with Bill Lay, Data Modeling with Barry Harmsen and Advanced Set Analysis with Oleg Troyansky.  I’ll also be showing off some cool analysis of metadata such as what Dimensions and Expressions your users are using in their Server Objects. 

 

 

Share

Displaying Keyboard Shortcuts

I note regular requests on QlikCommunity for Qlikview keyboard shortcuts. The usual reply is to post a list of shortcuts.

One of the useful tips I picked up at the recent Masters Summit in London was the key sequence for displaying the list of keyboard shortcuts. In an editor — either script editor or expression editor:

<Ctrl>qsc 

Keep the Ctrl key pressed, while sequentially pressing qsc.

-Rob

Share

An Example is Worth a Thousand Thread Replies

There’s a lot of information being exchanged on the QlikCommunity Forum http://www.QlikCommunity.com these days. Customers and Consultants ask technical questions, and other Customers, Consultants and QT employees provide very useful answers. Today’s post is a tip on how to improve the chances of your Forum question being answered quickly and accurately.

Many back and forth replies to a forum thread are about clarifying the question. If possible, post a qvw file example with your question. (I can’t, my file is too big! The data is private! Keep reading for ways to handle these concerns).

Reasons to post an example qvw:

  • An example will help clarify your question. The Forum is conducted in English, but English is a second language for many, if not most, of the Forum users. An example will provide additional understanding of your question.
  • More likely to get an accurate and complete response. Many questions require the responders to fiddle with expression or script syntax. If I have a qvw to work with, I’m more likely to test my answer before posting it, saving you the trouble of learning that I forgot a comma in my recommended solution.
  • Time. Most Forum members answer questions on a volunteer basis and their time is limited. For myself, I can only take the time to answer a limited number of questions. I’m more likely to pick the questions that are clear and provide the data I need. If I have to code up my own test data to work on the problem, I’m less likely to respond.

Some of the reasons you may be reluctant to post your qvw — size and privacy.

The maximum attachment size allowed on the Forum is 1MB. You can make the example qvw smaller by using the QV Data Reduction feature.

  1. Make some selections to reduce the number of selected values in the qvw.
  2. From the menu bar, select File->Reduce Data ->Keep Possible Values.
  3. Use File->Save As to save the reduced copy under a new name.

    If you use “Save”, QV will still open the “Save As” to help you remember not to overwrite the master copy.

You can protect the privacy of sensitive information, such as account numbers, revenue or customer names by using the QV Scrambling feature. In the menu bar, select Settings->Document Properties->Scrambling.

Here you can select a field to scramble and press the “Scramble” button to perform a random scrambling of the field . No one can determine it’s original contents. Like values will scramble to the same value which maintains the value linkages.

In some cases, you may still be unable to post your qvw even with reduction and scrambling. Or it may make your example more clear to post the data inline with your question. In that case, post your example data in the question using comma delimited format, so it can easily be pasted to a LOAD INLINE. For example:

Accounts:
AccountNo, Name
1234, ABC Corp
4567, DEF Co

Transactions:
AccountNo, TranId, Amount
4567, 1, 2000

One last tip. Before you post, remember to search for existing answers to your question. In the past, search on the Forum was not so robust. But QT has recently added an embedded Google search feature. This is great! It supports the full range of Google search operators. Try it. The “Google Custom Search” link is available at the top of each Forum page.

Finally, don’t forget to mark your question as “solved” when you’ve received a satisfactory answer.

Happy posting!

-Rob

Share