Category Archives: Visualization

qcb-qlik-sse, A General Purpose SSE

In Qlikview we have the ability to add function to the scripting language by writing VbScript in the document module (sometime called the “macro module”).  Typical  additions included regular expression matching & parsing,

Qlik Sense does not have the module feature, but both Sense and QlikView share a similar feature,  Server Side Extension (SSE).  SSE is typically positioned as a method to leverage an external calculation engine such as R or Python  from within Qlik script or charts.   The Qlik OSS team has produced a number of SSE examples in various languages.

SSE seems to be a good fit for building the “extra” functions (such as regex) that I am missing in Sense.  The same SSE can serve both Sense and QlikView.

Installing and managing a SSE takes some effort  so I’m  clear I don’t want to create a new SSE for every new small function addition.  What I want is a general purpose SSE where I can easily add new function, similar to the way QlikView Components does for scripting.

Miralem Drek has created a package, qlik-sse,  that makes for easy work of implementing an SSE using nodejs.  What I’ve done is use qlik-sse to create qcb-qlik-sse,  a general purpose SSE that allows functions to be written in javascript and added in a “plugin” fashion.

My motivating  principles for qcb-qlik-sse:

  • Customers set up the infrastructure — Qlik config & SSE task — once.
  • Allow function authors to focus on creating function and not SSE details.
  • Leverage community through a shared function repository.

I’ve implemented a number of functions already.  You can see the current list here. Most of the functions thus far are string functions like RegexTest and HtmlExtract that I frequently have implemented in  QlikView module or I’ve missed from other languages.

One of the more interesting functions I’ve implemented is CreateMeasure(), which allows you to create Master Measures from load script.  This is a problem I’ve been thinking about for some time and qcb-qlik-sse seemed to be a natural place to implement.

If you want to give qcb-qlik-sse a try, download or clone the project. Nodejs 8+ is required.  Some people report problems trying to install grpc using node 12, so if you are new to all this I recommend you install nodejs v10 instead of the latest v12.

If you are familiar with github and npm, you will hopefully find enough information in the readme(s) to get going. If not, here’s a quickstart.

  1. Install nodejs if not already present.  To check the version of nodejs on your machine, type at a command prompt:
    node --version
  2. Download and extract qcb-qlik-sse on the same machine as your Qlik desktop or server.
  3. From a command prompt in the qcb-qlik-sse-master directory install the dependent packages:
    npm install
  4. Configure the SSE plugin in Qlik.  Recommend prefix is QCB. If configuring in QlikView or Qlik Sense Desktop the ini statement will be:
     SSEPlugin=QCB,localhost:50051
  5. Start the SSE:
     ./runserver.cmd

The “apps” folder in the distribution contains a sample qvf/qvw that exercises the functions.

I’d love to get your feedback and suggestions on usage or installation.

-Rob

 

Share

Using JSON as HyperCube Data Payload

Summary: In this post I suggest a case for formatting measures as JSON to allow for easy consumption in an Engine-API webapp.

In a recent customer webapp project with my partners at Websy, I found that using JSON for data in Qlik hypercubes was a useful technique.

The app displays an overview of metrics collected throughout the organization, along with contextual clues and drill & focus capabilities.

The data consists of a varying number of metrics or measures  Measures may be added or removed by appearing in the data. Contextual data such as labels and color coding is provided in the data.

I can’t share the actual customer app.   Here is a simplified data example  to demonstrate with.

 

The real data contains additional complexities, but this sample will suffice.  Using this data, a section of the dashboard output would  appear like this:

 

A row is generated for each Category and columns are generated for each measure.

Current month values, (“Flag_Current=1”),  are displayed alongside available history (“Flag_Current=0”)  for the Date selected in the “Compare to” dropdown.

The CompareDirection is used to to control whether an increase in this measure should be scored as positive  or negative.

There is some asymmetry in the data.  Attributes such as “Format”, are available only in the Current data but are required in the History data as well.  History is available for some measures, but not others.  So what’s the best way to handle the asymmetries?  Not uncommon in Qlik, we can use set analysis and expression functions such as “Aggr()” or “TOTAL”  to propagate the attributes.

As the application requirements and data model grew, the expressions  and sets became more complex and difficult to manage and validate. This was true whether we provided the data as additional columns or qAttributes.

On the javascript side the row and column data are  processed into an object structure to prepare for visualization.  The type of propagation required here — assigning current.Format to history.Format — is trivial in javascript.

Since we are going to transform this data into objects, I thought why not deliver the data as objects already?

The text representation of a javascript object is JSON. Here’s what the Qlik table will look like with JSON output.

 

I created a Qlik script variable  to help generate the JSON.

Set AsJson = '"$1": ' & if(IsNum($1) and text($1) = num($1), $1, '"' &  $1 & '"');

The measure for the “Current Values” column generates an array of objects. The Measure expression is:

'[' & 
concat({1<Flag_Current={1}>} 
'{'
& $(AsJson(Date))
& ', ' & $(AsJson(Label))
& ', ' & $(AsJson(Value))
& ', ' & $(AsJson(Color))
& ', ' & $(AsJson(Format))
& ', ' & $(AsJson(SortOrder))
& ', ' & $(AsJson(CompareDirection))
& '}' , ', ', SortOrder)
& ']'

The definition for the “History Values” is  similar with only the set expression and the fieldnames changing.  The set expression to get this “collection” of data is specified only once and we don’t have to be concerned with repeating the set in multiple definitions.

Javascript to consume the data might look like this.  Note the JSON.parse() to consume the JSON.

const columns = [];
layout.qHyperCube.qDimensionInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
layout.qHyperCube.qMeasureInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
const senseData = layout.qHyperCube.qDataPages[0].qMatrix;

senseData.forEach(row => {
  let currentValues =   
    JSON.parse(row[columns.indexOf('Current Values')].qText);
  let historyValues = 
    JSON.parse(row[columns.indexOf('History Values')].qText);
  let category = row[columns.indexOf('Category')].qText;
  renderRow(category, currentValues, historyValues);
});

I find several advantages in the JSON approach.

  • Fewer expressions and sets to maintain.
  • A closer match between the HyperCube and the object model used in the app, making for easier understanding and validation.
  • Less transformation code on the javascript side.

I’m not suggesting that JSON is always the best way to deliver data.  We found it useful in this particular case and I wanted to share the experience so you could keep it in your tool bag as well.

-Rob

Want to learn more on this topic or advanced Qlik development?   Join us at the Masters Summit for Qlik in Amsterdam (28-30 Oct) or Washington DC (6-8 Nov). For traditional Qlik Devs, we’ll be teaching  advanced skills in data modeling and expressions in the QS/QV Track.  Experienced JS developers will want to attend the Qlik API Track for a deep dive into creating webapps and mashups.

Share

Art of the Possible: Dynamic Values

One of my goals in doing presentations and classes on Qlik Sense APIs is to get people to see the “Art of the Possible” — that is, seeing what opportunities may exist in your organization to leverage the power of the associative engine and the Sense APIs, beyond the standard client and hub.

Today I’ll highlight a demo built by my colleague Nick Webster.  This chart actually came out of a class when a student asked “could we…”.

This example uses a standard Qlik Sense table that shows Sales in both USD and Euro.  The twist is that the Euro column is calculated dynamically using an exchange rate fetched from a web service.

http://websy.io/demos/aop1 (select “Live Currency” from menu)

This visualization appears in a web page, but the data and the visualization (optionally we shall see later) are in Qlik Sense.

The Euro column is the sales amount multiplied by a variable representing the Euro rate. The Measure is:

Sum([Sales Amount]) * $(vEurRate)

Every 20 seconds a JavaScript function in the webpage makes a call to a web service to get the current USD/Euro rate.  The new rate is then assigned to the variable with a single API call:

app.variable.setNumValue("vEurRate", eurRate);

The chart will  refresh with the new calculation. Do we have to do anything to get the updated values to display? No! The visualization is automatically refreshed.  The API takes care of all the plumbing to detect the change and update the display.

Does this table viz need to appear in the Qlik Sense app?  It may be created and maintained in the app , in which case it is inserted into the web page with a single line of code:

app.getObject("pageLocation","objectId");

Alternatively, you can create the entire table visualization using JavaScript code in the web page.

You have options for reuse of existing content  and deciding where the code will reside.  For example, leveraging the skills of the Qlik Devs for expression authoring and visualization creation.  Or creating the visualization by using code only for tight integration with web development.  Or splitting the work where the Qlik Team authors Master Measures in the app and the Web Team builds code generated visualizations that utilize those Measures.

On December 3-6 I will be teaching a “Web Development for Qlik Developers” class in Atlanta.  In this four day class we’ll explore the “Art of the Possible” and get you enabled to begin implementing those possibilities as mashups and visualizations using the Qlik Sense APIs.

The class is geared to Qlik Sense Developers who have no prior experience coding web with web technologies.  We’ll start by learning the basics of HTML, CSS and JavaScript as they apply to Sense.  We’ll move on to reusing existing visualizations and create new visualizations from scratch, even using libraries beyond Qlik Sense.  The class is full of hands on exercises and you’ll come away with your work as well as many samples.

Along the way we’ll look at the requirements and options for licensing and deployment, and integration with popular web frameworks like Angular and Bootstrap.

You can register for the class here.  If you have any questions before registering, reach out to me on the Contact Form.

-Rob

Share

Distribution Plot in QlikView

Qlik Sense added a Distribution Plot visualization in the June 2017 release.   QlikView does not have a specific chart type for distribution plot, but you can achieve the same with a scatter plot.

The trick is to set the Y value (Expression #2) to a constant value such as “1”.  Here’s a distribution of Life Expectancy by Country (source: WHO 2017).

Dimension: Country
 X-Axis: =[Life Expectancy]
 Y-axis: =1

It works, but it’s difficult to understand how many points overlap.  You can switch the Style to the outlined ball similar to Qlik Sense and that helps.

I find a more effective technique is to add some transparency into the color.  Overlapped points will result in a darker color.

You can also highlight points using set analysis or alternate states.

 

Adding reference lines such as  Quartiles can provide additional understanding.

To add a second dimension e.g.  “Sex” (values: Male,  Female, Both)  replace the fixed Y-axis expression with an expression that generates an index number for the values.

=Dual(Sex, FieldIndex('Sex', Sex))

That will assign Y-values 1,2,3 to the Sex values.  The Dual() will ensure the text value will show in the popup. The Y-axis  will still display a numeric value so I’ve hidden the axis.  That leaves us without labels for the three lines.  We can either create labels using text-in-chart or use a color coding scheme.

Distribution plots can be oriented vertically by using a fixed X-axis. If you’ve used my ScriptRepository tool, you may recognize that the search results scroll-guide (the yellow dots) are a narrow scatter plot.

-Rob

Share

QDG Guru Day Inspiration

Lots of great presentations at the QDG Guru day in London last week.  Every talk gave me something to think about or explore futher.

Bruno Calver’s discussion of cohort / cell analysis (from his excellent white paper “Data Literacy –5 Practical Tips“)  and Patrik Lundblad’s discussion of multivariate analysis blended together (mashed up?) in my mind to inspire the example below.

The scatter chart below plots US agricultural commodity exports in year 2017.  Dimensions are Product and importing Country.   Taking a clue from Bruno’s talk,  I’ve concatenated Country & Product as the dimension to facilitate “cell analysis”.

X-axis is the absolute quantity in Metric Tons, Y-axis is the quantity normalized to population (thanks Patrik & Bruno) .

“Mexico-Corn” looks interesting. What should happen when I select the Mexico-Corn bubble?  Should the chart filter to show just that Country & Product?  Probably not, as that would be be a single bubble.

Regular readers of my blog know that I’m a fan of understanding user selections not as a filter, but rather a focus.

More likely I’m interested in exploring both Mexico and Corn as separate, but inter-related variables.  With a bit of set analysis I can display and color both series.  How about this for the results of clicking Mexico-Corn?

This strikes me as useful.  I can the understand the position of Corn in imports to Mexico, and the position of Mexico across all Corn importing countries.  See the chart subtitle for an explanation of the color encoding scheme.

Thanks again to to all the Guru Day speakers for the stimulating talks and the inspirations. Let’s do it again soon!

-Rob

You can download the example qvf here.

Share

Get the Numbers Right

I travel a lot for business and personal reasons. I’ve used TripIt (owned by Concur) for several years to plan and manage my trips.

It’s a  great tool, can’t image living without it.

Today TriptIt sent me a lovely graphic email summarizing my 2017 travel.  The design was  a very fashionable  card layout with share-to-social links.

Pretty cool, but for one problem.  It’s not my data.

Yes, those are definitely not my travel stats.

When I teach Qlik application development, a common  question raised by students or other newcomers is: “What’s the most important skill to master?  Scripting?  Expression writing?  SQL? Pre-attentive attributes? Color rules?”

My answer is always the same: “First, get the numbers right.  Second, keep the numbers right”.

It doesn’t matter how it looks or how fast it calculates if the results are wrong.

Join me at the Masters Summit for Qlik in Prague 3-5 April where in addition to a full range of Qlik Dev topics, we’ll be discussing automating quality.  I’ll be demonstrating tools to automate dashboard validation and continuous quality monitoring.

-Rob

BTW I emailed the TripIt folks about the error, they’ve promised to look into it.  

Share

Quick Sense App in a Page

Summary: Learn an easy method to deliver a Qlik Sense app as an embedded web page. Yes, the spelling of this post’s title is intentional 😉

I just came off a week of Qlik Sense API & Integration training with Nick Webster of Websy.  I’ve been nothing short of impressed with the integration and reuse options of Qlik Sense.

I was intrigued today by a question on Qlik Community asking if there was a way to disallow the application Edit mode in Qlik Sense Desktop.  The poster wanted his students to just “use” an app and not poke around in the design until a later time.  Kind of like the “distraction free” mode  in text editors.

The challenge I gave myself was how quickly could I knock out a solution to this problem?  I’ll detail my solution below and I’ll tell you it is taking me longer to write this blog post than it took to build the solution.

Rather than working at disabling or removing function I didn’t want, I approached the problem as including only what I wanted. My first thought was to create a mashup that represented the entire application.  Then I struck on an even easier approach — use the Qlik Sense “Single Integration API” with a bit of Bootstrap.   The Single Integration API does not require writing any code.  It’s just a URL that displays a single Visualization or complete Sheet.  You can create URLs and experiment with options in the Dev Hub Single Configuration Tool.

Bootstrap is a popular web design toolkit that makes it easier to create responsive and interactive web pages.  Thanks to Nick’s class I understood how Qlik utilizes bootstrap.

A basic URL to display a sheet looks like this:

http://server/single/?appid=myappid&sheet=sheetId&opt=options

Appid is a qvf filename on QS Desktop, or the GUID if using QS Server.  Options allow you to control  things like allowing selections.  A complete list is in the API doc.

With 50 lines of html I created a web page that delivers the full associative experience across seven sheets, including Current selections, Smart search and the Selections tool.

Here’s a screenshot showing my sheet navigation buttons across the top and the Sheet content, including Current selections, immediately below the buttons.

The html file is available for download here.  I used the “Sales Discovery” sample app but it is easy to adapt the file to any application.

There is a single iframe nested in a responsive Bootstrap container.  The buttons simply change the src attibute of the iframe to load a specific sheet .

If you want to try the file yourself you  only need to change the “var appid=” string to point to the app path on your machine.  No web server is required, just double click the html file to launch.  Your server or QS Desktop must be active.  Yes, this file works as is with QS Server.

Nick and I will be showcasing a variety of QS integrations at the Masters Summit for Qlik in Boston Oct 23-25.   Nick will also be presenting  a half day “Qlik Sense Integration” session at the summit that will teach you how to create a basic web page with bootstrap and explore more advanced QS integration options.

-Rob

 

Share

QV12 Variables with Alt State Fixed

Summary:  QV11 contained an inconsistency in how variables with equal signs were evaluated when using Alternate States.  This has been fixed in QV12.10.  Read on if you want the details. 

QlikView V12.10 includes an important fix to variable evaluation when using alternate states. A quote from the Release Notes:

In QV11.20 the variable was expanded in the first state encountered and this resulted in a random behavior when more than one alternate state was being used. Whereas in version 12 and up, the variable always belongs to a specific alternate state and this results in different behavior.

The random behavior described in QV11.20 has generated several interesting posts to QlikCommunity with responses of “I can reproduce” / “I can’t reproduce” and few clear answers.

I find the problem confusing and interesting enough to warrant an explanation and example beyond the Release Note.

What I am describing in this post only affects variables with a leading “=” in the definition, e.g. “=Sum(Sales)”.  These variables are calculated once in the context of the entire document. They are not calculated per row in a chart.

Let’s consider a variable named “vSumX” with a definition of “=Sum(X)”.  The expression simply sums all selected values of X.   Suppose we have two States in our document — “Default” and “State1”.  There could be two different selections for “X”.  Which set of X should the variable sum?

If we consider the variable definition in isolation, the answer is “Default set”  as there is no set identifier in the expression.  But what if the variable is referenced in an object in State1.  Should the State1 values of X be used?

No matter what you think the rules should be, here’s what was happening in QV11.20.  The variable was expanded (evaluated) in the first state encountered.  First state encountered means first state in the calculation chain, not something the developer directly controls.

Let’s look at some examples.   I’ve created a sample app (you can download here) with three States — Default,  State1 and State2.  The variable “vSumX” is defined as “=Sum(X)”.

With all objects on sheet in the Default state, selections in X would yield results like this.  (Note “$” indicates default state).

The first text box contains the expression “Sum(X)”.  The second text object contains the reference to variable vSumX.  The two values are what we might expect, summing the selected values of X in this state.

Let’s switch to a sheet that contains objects in the state named “State1”.

No selections in X and the first text object shows the expected result.  The second object shows the value of vSumX as previously calculated from the default state.  If we make selections on this State1 sheet, that will cause vSumX to be recalculated and both State1 and the Default sheet will reflect the State 1 number.  Is that correct?  Is it useful?  It’s at least consistent and comprehensible.

My next example is where the aforementioned “random” comes into play.  Let’s put objects from three states on the same sheet.

I’ve selected some values in the Default state of X and the results are what I might expect.  The value of vSumX is calculated from my last selections and the variable value is consistent across objects — there is only ever one value for a variable at a given point in time.

Now I select some X values in State1 and expect to see a new value (19) for vSumX.  But no change! The variable was expanded (evaluated) in the first state encountered which happened to be Default ($).

Now I select some X values in State2.  If the vSumX calc used my last selection I would expect to see 7.  But no, I see 19.  The State1 values were used.  If I repeat the exercise, it may use a different state to calc vSumX.  If you test you may get different results.  In this last example, State1 was used because it was the first state encountered in the calculation chain.  The order is not consistent.  It will be influenced by factors such as number of available processors and the order in which the objects were created.

Now that we’ve established that QV11,20 is broken in this regard, how was it fixed in QV12.10?  Simple.  QV12 uses  set identifiers as specified in the expression, without inheritance.

=Sum(X)

will use the Default State as there is no identifier.   If you want to Sum from a specific state, use it in the expression:

=Sum({State1} X)

Variables do not belong to any State.  Aggregation functions used in a variable may specify a Statename, just as chart expression do.  The difference is that the absence of a set identifier in a chart expression means “inherit the state from the containing object”.  In a “=” variable, no set identifier means “use the default state”.

A reminder that end of standard support for QV11 comes on Dec 8, 2017.  If you haven’t yet upgraded to QV12.10, I encourage you to do so.  Download my  QV12 Upgrade Considerations Doc as part of your planning process.  Feel free to contact me if you want some assistance with your upgrade.

-Rob

Update:  Qlik has extended support for QV11.20 to March 31, 2018. 

Share

Web Dev for Qlik Devs Course in San Francisco

In January I attended (review) Nick Webster’s  “Web Development for Qlik Developers” course.  I found the course extremely valuable so I’m bringing Nick  to San Francisco September 19-22 for a four day fast track course.

Are you a Qlik Sense Developer that wants to understand how to use the various QS APIs to create your own mashups,  portal integrations or custom content pages that leverage data from Qlik Sense?  Do you want to create your own QS Visualization Extensions or modify existing extensions?

In this four day hands on course you’ll learn:

  • The fundamentals of HTML/Javascript/CSS as they apply to QS Development and how to get started with some  popular frameworks and libraries.
  • Creating Visualization Extensions.
  • The differences and application of the various QS APIs e.g. Capability, Visualization, Engine.
  • Important QS core concepts such as the generic object model.
  • Connecting to the QIX engine to retrieve existing content or generate associative aggregations (hypercubes) on the fly.
  • Visualizing data using third party libraries.

You’ll come away with example code and your own completed exercises giving you the confidence to move ahead on your own.

No prior experience with web programming is required as the course will provide an intro to those technologies and how they are used in Qlik Sense Web Development.

Course fee, location and registration here.   Don’t wait, as the class is limited to 10 students!

-Rob

 

Share

Vizlib: Innovation and Agility

Summary:  As a provider of Qlik Sense visualization extensions Vizlib promises to blend the innovation and agility of open source with the reliability of commercial software.. 

The pace of delivery for visualization in Qlik Sense has been disappointing to some.  Thanks to rich open APIs in QS, much of the slack has been taken up by the community in the form of open source visualizations as seen on branch.qlik.com.   There you can see some remarkable innovation and responsiveness to community requests.

Should you use open source visualizations?  An open source extension may provide just what you need in your project.  But you should consider the unsupported nature of open source and evaluate the risk and consequences of the visualization possibly failing at some point.

When I worked as  a Java developer  my team used the open source Eclipse IDE as our main tool.  We also used a dozen or more open source plugins.  As our plugin library grew, we found that testing and updating plugins between releases was taking an inordinate amount of time, sometimes making us afraid to upgrade the base Eclipse product.   We  turned to a vendor and purchased a bundled version of Eclipse with dozens of plugins tested, supported and verified. Problem solved.

Vizlib is a new company is promising to blend the innovation and agility of open source with the commercial support that many customers demand.  Vizlib is partnering with the best extension authors to produce a library of fully supported high function visualization extensions. Check out some of what they have published so far:

  • A Table object that delivers the functionality of  a QlikView table and much more.
    • Rich Formatting Options just like in Excel/QlikView
    • Conditional show & hide of columns
    • Dynamic Labels
    • Minicharts & Progress Bars

  • An Advanced Text Object that provides the full functionality of the classic QlikView text object  (including actions!) plus additional functionalities like HTML code and icons. 

To date Vizlib has released five visualizations with more on the way. Check out what’s available on the Vizlib download page.  A free trial license is offered that allows you to try any of the extensions in your own installation.  Take a look!

-Rob

Share