Qlik Design Blog

7 Posts authored by: Elif Tutuk

Yesterday we announced QlikView 11.2. The main new feature in this release is QlikView Direct Discovery. With this exciting feature, QlikView does not require loading all data into the QlikView in-memory engine anymore. QlikView Direct Discovery is a hybrid approach that leverages both in-memory data and data that is dynamically queried from an external source.


Some of the main benefits of QlikView Direct Discovery are:

  • Consolidate relevant data from multiple sources, including Big Data repositories

In addition to the current decision support environments like EDW, data marts and operational data stores, organizations are now also considering the big data storage environments, such as Hadoop, BigQuery or Teradata etc. The challenge is enabling business users to analyze data from all of these data sources in the same analytics application. QlikView Direct Discovery offers a hybrid approach solving this challenge. It allows users to seamlessly run queries on the big data stores while they do discoveries on the data extracted from their ERP systems, data marts, EDW or even from their excel files into memory.

  • Maintain associations among all the data, regardless of where it is located

The level of details that is stored in the big data stores is usually very granular. Business users need an easier navigation means in this bulk of data. QlikView Direct Discovery leverages associations in the data, making extremely large datasets manageable.

For example, a policy analyst who uses a QlikView app to analyze regional loss and revenue information on a daily basis, would know the region names, but he would not have any clues on the specific policy numbers for these regions. With Direct Discovery, he can select the regional info, as he would do every day, and QlikView would automatically associate and query the billions of policy-level information on the big data store and display the query results with the in-memory metrics on the same QlikView app for the selected region.

  • Access Big Data without complex data modeling or programming

It is very easy to use Direct Discovery feature on a QlikView application. The only thing that is changed is to use “DIRECT SELECT” on the load script instead of using “SQL SELECT”. This new keyword would indicate QlikView that the data source is a direct discovery source. In this case, QlikView would only load the field names and will bring the data to the user interface when a direct discovery field is used on a QlikView chart. More information on the technical aspect of this feature can be found in this technical addendum paper. A how-to video is also available here.

Please note that QlikView 11.2 is a new release of QlikView, with one new feature, QlikView Direct Discovery. Please read the QlikView 11.2 Release Note document that is available on the download site before upgrading to QlikView 11.2 to learn more about the release and some of the bug fixes with this release. With this release, the QlikView 11 branch will be stopped and new service releases will be  made for QlikView 11.2 instead. For more information on this subject, please contact the QlikTech Support team.

Since we announced this new feature in October, we have seen great excitement in our customer and partner community as they see the potential of QlikView 11.2 enabling Business Discovery with Big Data, without any data size limitations. QlikView 11.2 is now available on our download site if you also would like to expand your business discovery capabilities on the data sets that were previously used separately, or not used, because of their bulk and the development effort required!

Yesterday we announced the beta version of QlikView 11.2. QlikView 11.2 is a new release of QlikView that will be available in December 2012. The main new capability of this release is QlikView Direct Discovery. We are very excited about this capability as it truly expands the potential use cases for Business Discovery, enabling business users to conduct associative analysis on big data.

Today there are many QlikView customers that use QlikView to analyze terabytes of data. QlikView’s patented in-memory data engine compresses data by a factor of 10 allowing associative in-memory analysis on very large data sets. King.com is one of these customers analyzing billions of rows of online gaming data with QlikView on top of Hadoop system.

Now with QlikView Direct Discovery, QlikView users can do Business Discovery on the external data sets without loading the data into QlikView in-memory data model. What’s really special here is that with this unique hybrid approach business users get the QlikView associative experience even with data that is not stored in memory. This is amazing!

One thing I would like to emphasize is the real value of QlikView Direct Discovery is its hybrid approach. It allows users to seamlessly analyze data from multiple sources (with the in-memory data) within the same interface without any size limitations. The users can associatively make selections in any of the data sets (in-memory or Direct Discovery) and always see what is associated and not associated with the same meaningful QlikView colors: green, white, and gray.

Another great advantage of QlikView Direct Discovery is the capability to query data for more up to date information in the use case scenarios where recency really matter.

How does QlikView Direct Discovery work? QlikView determines which data resides in-memory and which data is direct discovery data by using special script syntax, “DIRECT SELECT”. This allows certain data elements dictated by the script syntax not to be loaded into the QlikView data model during the script reload process, but still be available for query purposes in QlikView objects in the user interface and to be combined for analysis with the QlikView in-memory dataset. The video provides a short introduction on how to set up direct discovery. I highly encourage you to read the technical addendum paper to understand the best practices and some of the limitations that exist with the initial release.

I am personally very excited about this capability and cannot wait to get my hands dirty to try out different use case scenarios where the query results from big data sources can be leveraged with unique QlikView in-memory features!

Elif Tutuk

Accumulative Sums

Posted by Elif Tutuk Oct 12, 2012

We all use QlikCommunity when we need an answer for a QlikView question. We post a question and get an answer, usually in minutes, leveraging the expertise of 91,000+ QlikView users! QlikCommunity is the most-visited and active user community in the BI industry.

I also use QlikCommunity. And sometimes not to get an answer for a question but to think about new ways of doing things by reading the answers on the existing posts. So I have been curious about the answers on calculating the accumulative sums in QlikView and did a search on QlikCommunity. There are many answers provided as expected. Here is one way of achieving it by using rangesum() and aggr() functions.

It is a very common requirement to display the accumulative sum of a chart metric. The easiest way of doing this is to use the accumulate setting under the expressions tab of chart property.

Chart accumulation (2).jpg

Figure 1. Accumulate option on chart property


With this setting, the values of the selected metric will be accumulated. But sometimes, the requirement is to have the first data point accumulated as well. For example, to show the accumulated sales for 12 months where the first month displayed should be the sum of the previous 12 months and the following months should accumulate on top of this value by adding one month at a time. One way of achieving this is to use the rangesum() and the aggr() functions together.

Let us first look at Rangesum(). It is a range function that returns the sum of a range of 1 to N arguments Together with the above() function, you can calculate the sum of the last 12 months;

rangesum( above( sum(Sales),0,12) )

This expression will return the sum of sales evaluated for each month as they appear on the 12 rows above the current row.

The next step is to combine RangeSum() with  the aggr() function to aggregate the sum of sales for the 12 months at the month level.

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))

Using the Aggr() function, it is possible to calculate the accumulated sum for any other dimensions in addition to  the time dimension. For example, the 12 months accumulated sum of sales by store can be calculated as;

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Store))

Optionally you could also clear any selections on the month field as the calculation should reflect the accumulated sales for the previous 12 months, regardless of selections. This is achieved by using the set analysis “{$<Month>}”.

sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,12) ),Month))

One important thing to notice with this solution is the sort order inside the aggr() function. If the sort order is not correct, the aggregated sum of the 12 months would not be correct. QlikView always sorts the groupings (in this case the groups defined by the second parameter of the aggr() function) according to the load order. If the data is sorted by date during the load, QlikView will use this order and the aggr() function will show the correct totals for the desired time frame.

In summary, the accumulation option on the expression tab is the simplest way of achieving accumulations, but the use of rangesum() and aggr() functions are alternative ways of accumulating the data in more complex situations.

One of the common business requirements when analyzing the data is limiting the dimension values on a chart. For instance, limiting the chart to the top 10 sales people, or showing only the products that make up 80% of sales. QlikView 11 dimension limits functionality enables the business users to easily create these logics on the charts.

Dimension Limit.PNG

This functionality provides consistency across QlikView charts and more importantly better performance. Prior to QlikView 11, some charts in QlikView could display totals at the expression level, some can limit the number of dimension values to display and some can display an ‘OTHERS’ dimension value to catch those values omitted by the limits. With QlikView 11 dimension limits feature, these functionalities are consistent across chart types. Also, the conditions that are used on the dimension limits are calculated at the engine level, providing better performance and calculation time.

The functionality offers four main options:

  • Limits: This option restricts the dimension values displayed on a chart based on flexible criteria. The criteria are evaluated with the first expression of the chart. It is possible to restrict the dimension values by the largest, smallest, first, greater than or less than an exact value or relative the total.
  • Global Grouping Mode:  With this option, it is possible to treat the dimension values as either ‘local’ to the current upper dimension level in the chart, or ‘global’ to be evaluated across all instance of the upper level dimensions. This option is only applicable to the second or lower level dimensions on a chart.
  • Show Total: This option enables the display of subtotals at the dimension levels.
  • Show Others: This option displays an ‘OTHERS’ dimension value to catch those values omitted by the limits.

Each option has a separate set of applications and can be used in different permutations.   

QlikView 11 dimension limits functionality makes it easier for the business users to customize the QlikView apps based on their own analysis needs with just a few clicks!

Since we introduced the first version of the QlikView SAP connector 6 years ago, many of the QlikView customers leveraged the power of QlikView with the SAP data. Through QlikView’s unique, in-memory associative technology, they are able to make SAP based business decisions with the speed of the business.


The key enabler of the solution is the certified QlikView SAP Netweaver® Connector, which can extract data from SAP® R/3®, mySAP™, SAP BW, and BEX queries into QlikView. The extracted data  can  be combined with non-SAP data in one QlikView application providing a 360 degree view of the business.

Let’s take a look at a business case and understand how the QlikView SAP Netweaver® Connector would help. Assume a company has various SAP Netweaver data stores and its users would like to do sales analysis across their business.  With SAP Netweaver® Connector they can connect and extract data from;

  • SAP BW/BI Business Explorer Query for sales information
  • SAP Data Store Object (DSO) for additional order values and shipping weights
  • An SAP Query to gain insight into sales organization detail metrics
  • An SAP R/3 Table so they can integrate the company details into Google maps
  • An SAP Extractor for customer items
  • An SAP BAPI for business area lookups
  • An SAP Report for customer payments

They can then create visual and flexible analysis with this data in one single QlikView application which  can also integrate data from external non-SAP data sources.

QlikView provides pre-packaged QlikStart templates for SAP R/3 as well as QlikView SAP data dictionary and QlikView script builder applications. These apps would give a good head start for IT by delivering the background database information of how tables within SAP interact. It would also enable the business users to quickly realize their own data in ways they may not have envisioned before.


Last month we introduced a new version of the QlikView SAP Netweaver® Connector (SAP Netweaver version 5.7) with even more capabilities. If you would like to have a jump start to do Business Discovery with your SAP data, you can download the QlikStart templates from QlikCommunity.

After doing a couple demos recently, I noticed that some of our customers are not aware of the repository panel. It came to me as a surprise as repository panel was introduced with QlikView 10. The repository is a QlikView app level entity that lists all sheet objects, dimensions and expressions that have been used within the current app. 

The repository panel is an AJAX client only entity.  A user can view the content of the repository and elect to reuse any existing chart or definition by dragging and dropping that definition out on to a sheet.  A chart can be pulled from the repository panel.  Exposing its properties, a user can then change the dimensionality or measures within the chart.  Instant feedback gives the user immediate insight into what they have created.

How does using the repository panel benefit the users? A common reaction from IT and business process owners alike is that they may not trust their users to have the know-how to create QlikView objects. The repository panel aims to eliminate these fears by giving an opportunity to train users to reuse what is already in their app.

On my previous blog post, I explained how to setup comparative analysis on a QlikView app. I have been thinking on how to make it easier for the business users to assign QlikView objects to different alternate states and I came up with this idea of using the container object and the repository panel. The video shows the details of setting up the solution.

In the solution, I used multiple container objects where each of them is assigned to a different state. I set the presentation type of the container objects to grid mode. By using the repository panel, the users can drag and drop different objects into the container objects. As objects in a container object inherit the alternate state, the user can then create different selection states in each container object and visually compare the charts for these different selections.

This solution is a great example of showing how QlikView enables business users to remix and reassemble data in new views and create new comparisons on the fly for deeper understanding!  

One of QlikView’s key differentiators is the associative experience, the ability for business users to easily navigate through data sets to not only find answers to their questions, but to also discover new insights,  and spot hidden trends. Only QlikView provides business users with this level of flexibility and insight.

Once the business users find a key insight or trend though, they might want to compare that with a slightly different view. For example, if they find that bike and accessory sales in Europe have flattened out over the last few quarters, they might wonder how that compares with the rest of Europe. Of course with QlikView they could immediately select the other European countries instead of France and immediately get the answer. But what if they wanted to see those two or more different views side by side?

One of the approaches that are used in those situations is using set analysis. With set analysis, it is possible to create data groups in charts and use them for visual comparison. The limitation of set analysis is the person creating set analysis should know about the type of groups that the other users would like to compare and set it up in advance accordingly.

QlikView 11 Comparative Analysis (Alternate State is the technical name of the feature) overcomes this challenge. The goal of Comparative Analysis is to make it easier and flexible for business users to see two or more data sets in the same application, alongside each other in the same graph, in graphs next to each other, or even as reference points for calculations and comparisons.

Comparative Analysis is a developer enabled capability, meaning developers need to set up the basic framework for comparison in an application. But it is a user-driven feature; meaning business users can then define the selections they want to compare. This video shows how to create alternate states, assign QlikView objects to them, and the concept of inheritance of alternate states. I will post about more creative ways of using alternate states in a couple of weeks, stay tuned!

Filter Blog

By date:
By tag: