As your QlikView deployment grows in size it can quickly get unwieldy unless you start imposing some sort of structure and standards to it.
In Computer Science we sometimes talk about Separation of concerns, http://en.wikipedia.org/wiki/Separation_of_concerns, although it is not 100% applicable for QlikView it does make sense to try to split the different pieces of a QlikView document apart into re-usable components.

 

 

To help you with that QlikView has the functions Include and Must_Include.

 

Essentially what this allows you to do is to create reusable snippets of load script that you can then re-use across your deployment. All of those connection strings to databases are prime candidates for a include statement, 1 statement that can be referenced from multiple documents and 1 place to change it when your connection string changes.

Connection strings are just one example, I've used it in the past to store the corporate graphical profile as variables that I can then include in all my documents or why not a standard calendar and date formats?

 

Our own Qlik Deployment Framework also heavily relies on Include statements to allow apps to be portable between different servers and to promote code re-use. You can join the QDF group here, Qlik Deployment Framework


So how do I use it?
To quote the help file,

$(Include =filename )
$(Must_Include =filename )

 

 

The include and must_include variables specify a file that contains text that should be included in the script. The entire script can thus be put in a file. This is a a user-defined variable.

The difference between include and must_include is that include will fail silently if the file is not found during script reload, while must_include will throw an error if the file is not found.

Examples:
$(Include=abc.txt);
$(Must_Include=def.txt);

 

 

We make use of dollar sign expansion to expand the contents of an external file into our load script.
The file path supports both absolute and relative paths making it ideal for portability between test and production servers.

 

If you ever were a fan of Inception then you could start structuring your Include statements with nested dollar sign expansions to manage where to load the Include files from,

 

let env = '<A absolute or relative file path, for example ..\Config\Test\>';
$(Include=$(env)\ConnectionString.txt)

 

This will also be my last post for the Design Blog, unless I make a guest appearance , so I hope you have enjoyed reading my posts as much as I have enjoyed writing them.

In the words of the Fugees; Ready or not, here I come.
Be warned though, this will be a wall of text so grab yourself a cup of coffee, sit back, relax and immerse yourself into the world of code and a little history lesson.



A brief history of time

 

With the release of QlikView 10 we shipped the possibility for developers to build their own QlikView objects using web technologies like javascript, HTML and CSS. These objects could leverage the power of the QlikView association engine, making selections and behave just like standard QlikView charts would. Did you need a specific chart for this specific project? No problem, now you could extend the standard set of charts with something custom and specific just for you.

So was it a huge success out of the gate? Hell no.
Not only did this introduce a new skillset that previous was not common amongst a BI team but back then the browser landscape was extremely fragmented and inconsistencies between them was many.

Things started to pick up as QlikView 11 was released (adding support for Document Extensions) and a huge improvement was made both to the documentation and the many examples that started to ship with the product. I guess it was also around this time that things really started moving outside of the Qlik-o-sphere. Infographics, charts on the web and javascript frameworks became more commonplace, normal people’s interest in data spiked and development time on the web was greatly reduced.

 

Over the years we have seen innovative mapping solutions for QlikView entering the market, lots and lots of new charts has been developed and are available for free and under open source licenses. Mind you this is not something Qlik has developed, we merely produced the tools and the community stepped up and has produced some amazing things.

 


Fast forward to 2015 and beyond


Charts and data in the real world has become more and more common place, web technologies has advanced tremendously and we are soon reaching an evergreen state of web browsers allowing developers to leverage the greatest and latest features the web has to offer. Visualization frameworks and libraries are popping up everywhere making it easier and easier to chart data which in turn also puts pressure on us, Qlik, to be as open as possible and be able to integrate with these libraries.

 

Qlik Sense has also been released for almost 6 months and that marked a milestone for us, not only is it a new product but it was also built with the mindset that everything a software developer at Qlik is able to build should be built on an open and documented platform that anyone should be able to leverage.

 

We no longer make a separation between things built by Qlik and visualizations produced by a partner or a customer, we give each visualization an equal amount of weight.

 

We also took the opportunity to do a little name change, because naming things are important.
No longer is it called Extensions, as in an extension of the product, but instead we simply refer to it as Visualizations.

 

Enough with the ranting, let’s write some code!

So what skills do you need

  • A understanding of HTML and CSS
  • Basic to advanced javascript knowledge

 

What software do you need

  • Qlik Sense Desktop
  • A text editor (Optional)

 

This video will give you a short introduction to the workbench editor that ships together with Qlik Sense Desktop and showing you how to build your first visualization.

 

 

Additional assets:
http://help.qlik.com – Documentation
http://branch.qlik.cm – Developer Community to share and collaborate on projects

 

Who can forget when Steve Ballmer went a little bit crazy on stage and showed his appreciation for developers.
We also try to show our appreciation, although perhaps not as flamboyantly.


Qlik Community is an amazing resource where QlikView and Qlik Sense users and developers come together to share ideas and apps, help each other and form relationships. It is not only amazing but also one of the most active product communities on the web!

This fall we also launched Qlik Branch, a meeting place for the developers that extend and customize our products. Branch aims to bring together those people that program against our APIs and the projects they are working on. Developers can share code, collaborate on projects and publish solutions to the repository.

Qlik Branch is strictly open source only, meaning that any project you find on the site will be free to use and you are free to contribute to the code as you please.

Don’t know how to code? Don’t fret; Branch is also a repository for solutions that you as a user or developer can leverage free of charge, just go ahead and download them and plug them in!

We believe… No, we know, that by giving our community the tools, support, and areas for collaboration you will create amazing things we could never have dreamed off.

 

So what are you waiting for? Head on over to Branch and check it out!

QlikView has extremely powerful load script syntax which in 99% of the cases will solve every problem you have or might encounter. But sometimes you run into one of those edge cases where the standard load script syntax is not enough or the end result would be un-maintainable.

 

I recently ran into one of those cases when I was analyzing first names in the US. Typically with both first and last names you have many different variations of the same name.Take Susanna for example, in Sweden it’s probably Susanne or in Russia Syuzanna. Although spelled differently they do belong to the same family. But how should I group them together in QlikView?

An unlikely hero to the rescue

 

I settled on implementing a SoundEx algorithm, http://en.wikipedia.org/wiki/Soundex, which is commonplace in most database software nowadays. However, being somewhat lazy I quickly realized that replicating the algorithm using load script syntax would take me forever so I settled on something different.

 

A Google search later I had discovered the SoundEx algorithm implemented in JavaScript, http://creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm#JavaScriptCode.

Perfect, just what I needed!

The QlikView macro module supports both VB and JScript (Microsoft’s variant of JavaScript) so I could easily copy and paste the algorithm as a new macro module in QlikView. Now from my load script I can call that module during reload as such,

 

Load 
  Name,
  //Call macro module SoundEx() – returns soundex code.
  SoundEx(Name) as SoundEx
From …

 

Great! Now all the loaded names have a SoundEx code attached to them which would allow me to count the names within those groups instead of only basing my analysis around the count of a single name.

 

This was just one use-case for how you could leverage macro modules during reload.
Seeing as JavaScript also has implemented regex you could also leverage macro modules to do phone number or address validation. With the recent explosion of open source JavaScript code on the web, chances are someone before you already built a module of what you are looking for.

 

A final word or warning, do exercise caution and don’t push all your logic into macro modules just because you can.
Also before you consider using macros for the front end, read this excellent post by HIC http://community.qlik.com/blogs/qlikviewdesignblog/2014/04/28/macros-are-bad

Sometimes you want QlikView to open with a specific set of selections, apply a bookmark or perhaps even deep link to a specific sheet.

A typical use case could be to embed an entire app or a single object inside a CRM or ERP system and depending on the context, current customer for example, filter the QlikView app to only show records related to that specific context.

 

So how do I use this black magic?


One approach would be to use triggers with the obvious downside being that the trigger would always fire regardless of how you opened the app.

Another approach is to supply a set of parameters to the URL for that specific app.

 

Let’s take an example, the Sales Compass demo from the demo site. Below us the URL to access the app and the different components explained.

 

Actual URL

demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11

 

Explained URL

<host name>/<virtual directory>/opendoc.htm?document=<url encoded full name for the application>&host=<name of QVS>

 

In addition to this URL you can also supply some extra parameters to control which actions will fire when the app is opened. For example the URL below will open the Sales Compass app with the value “Q2” selected in the listbox with id LB5699 (yes we create way to many objects )


demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11&select=LB5699,Q2

 

Of course this is only a simple example, in the table below you will find all the available parameters you can append to your URL.

Feel free to mix and match these til your hearts content.

 

ActionParameterExample
Select a single value&select=<Listbox ID>,<Value>&select=LB01,Total
Select multiple values&select=<Listbox ID,(Value|Value2)&select=LB02,(2011|2012)
Open the app on a specific sheet&sheet=<Sheet ID>&sheet=SH01
Open the app with a bookmark applied&bookmark=<Bookmark ID>&bookmark=Server\BM01

 

Wait a minute, you mentioned single objects?

Ah, yes! When QlikView 11 was launched we also introduced the capability to display a single object from an app.

This allowed customers to integrate objects from different applications into a single view in a external system. It is also this screen that powers the small devices client.

 

Substitute opendoc.htm with singleobject.htm and specify the object id you want to display,

demo.qlik.com/QvAJAXZfc/singleobject.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11&object=CH378

 

And voila! You now have a fully interactive single QlikView object!

When you start getting into the Data Visualization field you quickly learn that there are good visualizations and there are bad visualizations. Most scorned are probably the horrible pie chart and its cousin the donut chart. Should we follow Stephen Fews advice and save the pies for dessert or is there a time and place for sub-optimal visualizations?

 

pie.png

 

With data visualization celebrities such as Edward Tufte and Stephen Few being very vocal in their crusade against bad visualizations the rest of the industry has started to follow suit. BI vendors have slowly adopted and almost everyone is promoting data visualization best practices now a days.

 

I'm not saying they are wrong, a bar chart or line chart for time series are always a better option than one or several pie charts when the core objective is to compare data points.

 

But is that always the core objective?

Sometimes we build QlikView apps for very large audiences, apps they might only use once in a while, apps that aren't critical for them to perform their job and sometimes we build apps that contain downright “boring” data. It’s still an important app; the users would more than likely gain additional insight from the data or the app would help them perform their job more efficiently.

 

Looking at myself I know there are probably several applications that Qlik has deployed internally that could help me in my job. They aren’t critical for me, I would probably only look at them once a quarter or less but still I don’t open them at all.

 

These would be apps with “boring” data, apps built according to every best practice in the book. Absolutely no pie/donut charts, muted downplayed color series from http://colorbrewer2.org/ and consisting to 99% of bar charts and data tables. They are in no shape or form bad apps, they are built around solid best practices, every data point is correct and every visualization carefully selected to achieve the maximum efficiency but still I can’t get myself to spend more than 15 seconds in them.

What they are lacking is attention.

 

Attention vs Accuracy

I want to make the case that sometimes it’s appropriate to sacrifice a certain degree of accuracy for attention. Sometimes you need some sex and sizzle to get your users to care at all.

 

For example, humans are naturally drawn to rounded objects versus squared yet our brains are not wired to quickly grasp the sizes of a pie chart. Despite the logical part of my brain telling me that the bar chart would be a more optimum medium to display the information my eyes are still drawn to the pie chart.

 

The Bar Chart makes it easier to compare the individual values against each other but for me the pie chart is more inviting.

charts.png

This holds true for pie charts and it also is true for maps. In this day and age as soon as we have an address or a location in our data we are compelled to put it on a map. Why? Most of the time the geospatial dimension is totally irrelevant to our analysis but we still squeeze a map in every application that we can. Because maps engage the users - you can put almost any boring data set on a map and I would still explore it, I will most likely not gain the most knowledge out of the map BUT my interest has been sparked and I might explore the data and the app further.

 

So should we go wild and crazy, sprinkle every app with pie charts, donut charts, bubble charts or maps?

Absolutely not, while at the same time we need a certain degree of attention to get our users to take interest in the data; we also have a responsibility to represent the data in the most accurate way possible. But what good is the data if people won’t take any interest in it at all?

 

I say it’s okay to stray from the path of best practice as long as you are aware why you are doing it.

 

Michael Anthony has previously blogged about Progressive Disclosure which can be used to overcome the initial attention hurdle while the rest of the application can focus on delivering as accurate representation of the data as possible.

 

TL;DR Pie charts - bad. But sometimes good.

You wake up in the morning and head down to the living room; faintly you hear a lingering “hohoho” from the chimney. Santa was here – and he left us something wonderful!


Santa is early this year, not only did he bring us a new and shiny service release for QlikView he also included a free to use mapping extension!

 

When you install the latest version of QlikView, http://www.qlik.com/download/, we now ship an Example Extension Object that makes use of OpenLayers and MapQuest.

So how do I make use of this sweet nectar you say?
Let me take you on a journey and explore some mapping possibilities!

  1. Head out and install the latest version of QlikView, make sure you install the examples.
  2. Navigate to C:\Program Files\QlikView\Examples\Extensions and double click the “Extensions Examples.qar” file. This will install all of the extension examples.
  3. Open the QlikView “Extension Examples that you can find in C:\Program Files\QlikView\Examples\Documents\
  4. In the Mapping tabs you will find examples on how to plot either dots/points, lines or polygons.

 

map.png

 

Attached to this post you will also find a dataset and an app that contains all of the high speed cameras in Sweden with corresponding latitude and longitude points if you want to play around with the extension, make sure you install the extension first, on your own.

 

Keep on Qliking!

 

Keep in mind

Extensions are generally built upon web technologies such as HTML and JavaScript and for QlikView to be able to render these objects on the screen you will need to run QlikView Desktop with WebView mode enabled or access the document through the AJAX-client over AccessPoint. The IE-plugin does not support extensions.

 

Disclaimer

The QlikView Mapping Example Extension can be configured to use many different map tile sources.

Each map tiles source has its own terms and conditions and the user assume all responsibility for the selection of a source for map tiles and for compliance with the terms and conditions of the selected source. Any and all liability associated with the selection of a tile source and the compliance with the terms and conditions of the selected source is hereby disclaimed.

2 years ago I was certain that the tablet craze would not reach the business world.

I was convinced that our smart phones were too small and limited to provide any real business value.

I was wrong. Dead wrong.

 

Since then we have stopped talking about mobile or desktop, instead we talk about software. We expect the software we use to work everywhere, anytime and on any device.

 

Luckily QlikView makes it easy for us; the AJAX-client works just as well in the browser as it does on a tablet or even on a smartphone.

 

However every device comes with its own screen real estate so if we optimize our apps for a desktop experience our tablet users will be less than pleased and vice versa. Technically the app would work on all devices but if you were on an iPad perhaps the buttons should be a tad bit bigger, the width and maybe the length of the app smaller and so on.

 

So how do we achieve this without having to deploy one app optimized for every device/screen?

Not only would that be a maintenance nightmare but it would also scale horrible as we would potentially have to load the same application twice into the working memory.

 

 

ClientPlatform() to the rescue!

 

ClientPlatform() returns a string containing the platform the user is using, see table below.

With this information we could switch between a dashboard optimized for a desktop experience and a tablet experience depending on the device the user is using at the time within the same QlikView application.

 

For example using WildMatch(ClientPlatform(),'*mobile*') = 1 in a Show Condition would enable the sheet or object for mobile devices but it would be hidden for a desktop user.

Now we can design our apps to cater for a perfect user experience regardless of which device the user is using.

 

If you own an iPad you can see it in action by visiting our demo site and browse to the Pro Golf app which will change look and feel between a desktop and an iPad.

 

 

This is a few examples on what the ClientPlatform() can return.

 

Browser

ClientPlatform()

Internet Explorer <VersionNumber>

browser.MSIE <VersionNumber>

Google Chrome

browser.chrome

Firefox <VersionNumber>

browser.gecko <VersionNumber>

 

 

iPad

browser.safari.mobile

Android Tablet

browser.android

Have you ever been asked to create a table that has several independent calculations over different metrics?

Mixing aggregation formulas and counts in the same table?

Did you end up creating different tables for every view point on the same information?

Or did you create a table like the one below?

table.PNG

If not, let me introduce you to ValueList() and its number oriented big brother ValueLoop().

 

ValueList
ValueList (value {, value })

ValueList allows us to specify a set of arbitrary values within the function, when used as a calculated dimension in a chart this will act as a synthetic dimension.

We can later restate the same function, with the same parameters, in our expression to reference the corresponding value in our newly created synthetic dimension.

 

And it is as simple as creating a straight table with following dimension and expression

 

Calculated Dimension:

=ValueList('My First KPI','My Second KPI')

Expression:

=IF( ValueList('My First KPI','My Second KPI')='My First KPI', 
     Sum([My First KPI Field], 
     Count([My Second KPI Field])
)

 

And voila we have created a table/chart with a dimension that does not exist in our data model and with an expression that has the possibility to mix and match aggregation functions over each dimension.

 

Matthew Crowther has also created an excellent Explosion Chart that also leverages ValueList, you can read more on his blog

 

ValueLoop
ValueLoop(from [, to [, step = 1 ]])

ValueLoop shares the same characteristics as it’s little brother ValueList with the exception that it will create a series of numbers as the synthetic dimension.

 

To create a dimension with values that spans between 1-100 we would create a calculated dimension with

=ValueLoop(1,100,1) 

which we can reference from our expression with the expression

IF( ValueLoop(1,100,1)=3,
     'Almost Pi',
     'Not Pi'
)

 

ValueLoop also allows us to create the, not so useful but fun to make, square pie chart which you can read more on in this technical brief.

Filter Blog

By date:
By tag: