How To Chart Multi-Dimensional Running Totals In QlikView

Learn More About Software Selection

What we did to take advantage of the scripting language in QlikView to extend capability and generate a three dimensional cumulative total chart on a two dimensional plane.

An example of how to take advantage of the powerful scripting language in QlikView to extend capabilities.

What the client needed:

One of our Fortune 500 clients had a very specific requirement: they wanted to visualize an important measure stratified by Region and Rating as running totals over Time.  We agreed on a mock-up to generate a three dimensional cumulative total chart on a two dimensional plane.

The seemingly impossible hurdle:

We know QlikView offers useful grouping and summation functions as RangeSum, Aggr and logical Set-Analysis, but this would not help to generate graphics showing multi-dimensional totals.Although QlikView has a built-in RangeSum function for such use, it seems to work out of the box only with a single dimension (such as running totals by month).

What we did:

We wrote an algorithm taking advantage of the scripting language in QlikView to extend capability for the desired output.

Figure 1- Example of data to be visualized as running totals across multiple dimensions

Figure 1 is an example of a data-set for illustration.  On the left it shows counts by Rating (a discrete number with 1, 2, 3, or 4 as possible ratings) by Month by Region—this is the non-cumulative version.  On the right it shows the same information as running totals stratified by multiple dimensions.

A Swing, and A Miss

Initially we tried to simply use the built-in RangeSum function as an expression in a bar-chart. However, we discovered that the running totals were inadvertently being dropped off for regions that did not have data across all months and ratings.  Therefore, the function was not working accurately for multiple dimensions, such as the example shown in Figure 1.

Research And Hypothesis

As a next step, we did some research and came up with a hypothesis that because data points did not exist in the source data for all combinations of the dimensions involved, that the chart just was not rendering as intended (for example in Figure 1—Region “c” has a data point for Rating “2” in January but not in February or March).

Data Script/Algorithm

To test our hypothesis we used the following data script to generate a Cartesian product or a cross-join with every possible combination of the dimensions involved.  We added a column called “one” in the script with a value of 1 or 0, with 1 for actual data and 0 for data we induced using Cartesian products across region, rating, and month.Example of a script for generating Cartesian products to enable multi-dimensional running totals:

Bar Chart Creation

We created a bar-chart in QlikView with Month, Rating, and Region as the dimensions.  The expression for the stacked bar-chart is computed using a RangeSum function aggregated by the three dimensions, as shown below.

 =Aggr(RangeSum(Above(sum({}one),0,RowNo())), Region, Rating, Month)

The max value for the y-axis is dynamically computed to show max value for a rating in any month using a max function over the aggregation shown below:

 =max(Aggr(RangeSum(Above(sum({}one),0,RowNo())), Rating, Month))+1

By obtaining a deeper understanding of the data structures, forming a hypothesis, and writing algorithms we were able to tinker with and iteratively extend capabilities in QlikView allowing effective presentation of data. The client plans on using this visualization to launch an enterprise dashboard for their operations improvement initiatives.

Analytics and big data. It's what we do.


Contact Us

National Office Telephone | Mon-Fri 8:30am-5:30pm CT

Data Strategy Session

To thrive with your data, your people, processes, and technology must all be data-focused. This may sound daunting, but we can help you get there. Sign up to meet with one of our analytics experts who will review your data struggles and help map out steps to achieve data-driven decision making.

Learn More →

Fill out this form to get a 30-minute Data Strategy Session with one of our analytics experts.

Cloud Strategy Session

In one hour, get practical advice that you can use to initiate or continue your move of data and analytics workloads to the cloud.

During your free one-hour cloud strategy session, we will:

  • Review how the cloud fits into overall corporate strategy
  • Review how the cloud fits into data and analytics strategy
  • Review existing cloud assets
  • Discuss data and current analytics solutions to prioritize what components should be moved to the cloud

Fill out this form to get a one hour Cloud Strategy Session with one of our analytics experts.