How To Chart Multi-Dimensional Running Totals In QlikView

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.

Sandeep Sarawgi's photo
Senior Consultant
Sandeep is a data science enthusiast and consultant at Analytics8 with experience from Main Line Health, Duke and Stanford University, and Children’s Hospital of Philadelphia. He enjoys working on data architecture and dashboard design projects for operations, geospatial mapping, social media analytics and more. During his free time he enjoys exploring nature and going on hikes.

 

Contact Us

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