The following screenshot is an example of what happens when the field that we use as a dimension has no relationship with the field(s) that we use in an expression:

This issue is especially common when we are making quick adjustments to a data model and delete a key field or rename it in only one table, thus breaking an existing link. Another reason may also be that we mistakenly add a field from a legitimate island table to a chart.

When we notice the issue illustrated in the previous chart, our first action should be to look at the data model and confirm whether the tables are linked. If they are in fact not linked, then we fix this error by linking the tables in the script. If they shouldn't be linked, we change either the chart's dimension or expression to contain fields that are related to each other.

When business users export QlikView charts to Excel, they may occasionally report that the sum of the rows in Excel does not match the total in QlikView. For example, the total in the QlikView chart in the following screenshot does not equal the sum of the rows:

We can confirm this discrepancy when we export the table to Excel and calculate the sum of the rows.

A common (and incorrect) fix to this error is to change the way that the chart calculates the total. If we change Total Mode to Sum of Rows, we will fix the problem in this particular chart, but we are most likely ignoring an underlying problem with the data model. We should always use Expression Total as Total Mode:

The sum of the rows doesn't equal the expression total because the chart uses fields whose tables have a many-to-many relationship. For example, in the case of the previous example, the error is caused by an item that is assigned to two different item groups. Therefore, two rows in the Items table are linked to the same multiple rows in the Facts table.

If the item is supposed to be in two groups, then we may need to add a business rule to prorate the amount between the two groups. For example, we could prorate the total sales amount so that 40% is assigned to Widgets and 60% to Whatchamacallits. However, in most cases, this issue is caused by poor data quality or an error in the load script.

List boxes always show a list of unique values. However, as in the following list box, we sometimes come across ones that appear to contain repeat values. Before we start proclaiming that we've found a bug in QlikView, let's review how QlikView handles data types.

We rarely have to worry about data types in QlikView. For example, we don't declare fields to be a varchar, nvarchar, int, double, or text data type like we do in SQL. In QlikView, the only thing that we have to remember is that every field value is a dual data type or, in other words, it has two values: a string, and a number.

The string value is the one that is displayed in a list box and it's possible that the same string represents different numbers. Even if the numbers have the same string, the list box will not group them into one entry. We force the list box to show the number values that are paired with each string using the following options in the Number tab of the list box's property dialog window.

We can now confirm that the dates represent distinct numbers. We expect the number, 41639, to correspond to the string, 12/31/2013. However, the fractional part of each number is different because it represents a particular time during the day.

The source of this issue is in the load script and this is where we should fix it. In this case, the Date() function, which manipulates the string part of a field value, is used to format a field value that contains a timestamp. Although the string part of the values is formatted as expected, the number part of the value remains the same. If we want to convert a timestamp into a date, then we first need to convert the number part from a decimal into an integer using the Floor() function and then format the string using the Date() function. For example, we use the following code in the load script to fix our example:

…
Date(Floor(CreateDate),'MM/DD/YYYY') as CreateDate,
…

Business users often report that the numbers in QlikView don't match their expectations or their own manual reporting. Given QlikView is where they visualize data, this is going to be where they detect numerous data-related issues even if the problem originates in the data source.

When business users report data discrepancies in a stable QlikView application, our first step should be to follow the data's lineage to its source. If the source is correct, then we break down the problem into the different steps of the same path that the data follows until it reaches the user—extraction, transform, model, and visualization.

Along with creating a well-designed folder structure for our QlikView applications, there are a couple of tools that can help us understand the exact path that data takes from its source until its visualization. The first tool is QlikView Governance Dashboard, which you can download from Qlik Market (https://market.qlik.com/qlikview-governance-dashboard.html).

QlikView Governance Dashboard offers a complete overview of a QlikView deployment. Once we've entered in the necessary information in the Configuration tab and then reloaded the application, we can review data lineage in the Lineage tab:

In the Lineage tab, we can select a source table in the Sources table and take a look at which QlikView files use this table in the Processes table. We can also review whether it is used to create a QVD in the Generated QVDs/QVXs table. In the same way that we navigate from the source table to its target, we can also begin our analysis by selecting a target table in Generated QVDs/QVXs and investigating which process generates it from which possible sources.

The second tool is the DataLineage subroutine in QlikView Components (QVC) (https://github.com/RobWunderlich/Qlikview-Components). In the same way that we used a QVC subrountine to create a master calendar, we first include the QVC library in the load script of the QlikView application whose data lineage we want to analyze:

$(Include=..qvc_runtimeqvc.qvs)

Then, we call the DataLineage subroutine:

CALL Qvc.DataLineage; 

Finally, we call the Cleanup subroutine to clean up any global variables:

CALL Qvc.Cleanup; 

Once we reload the application, we can create the following table that details the application's data lineage:

These two tools can help us discover data lineage at the table level. Once we understand it at this level, we analyze it at the field level by reviewing the load scripts of each QlikView application involved in the process.

Once we have an idea of the data lineage, we walk through the following steps to diagnose the issue:

Hopefully, we will find the cause of the issue in the first few steps and fix the problem quickly. If not, then at least by the time we get to the visualization, we can be confident that the problem is there.

The following table shows the total sales and average monthly sales by customer and item group. However, common sense tells us that if a customer has an amount in Total Sales, then there should also be an amount listed in Monthly Sales Avg and not a null value. It also seems strange that the Monthly Sales Avg values that do appear are larger than the Total Sales amounts on the same row.

In the Monthly Sales Avg column, we used the following code with an aggr() function to calculate the average monthly sales of each customer and item group:

avg(aggr(sum(Amount),Month)) 

However, the dimensions in the aggr() function should always include the same fields that are defined as the chart's dimensions. If we change the expression to include Customer and [Item Group] as parameters to the aggr() function, then we get a table with the correct numbers.

avg(aggr(sum(Amount),Month,Customer,[Item Group])) 

Set analysis is a powerful tool, but it is not a panacea for every analytical need. The following table is an example of a chart that cannot be created using set analysis:

The false belief that set analysis may be the way to create this chart is born from its ability to create the following chart that contains the same monthly and accumulated sales columns:

In this chart, we used the following code to calculate Accumulated Sales:

sum({$<Month={"<=$(=max(Month))"}>} [Net Sales]) 

When we use a chart dimension in the set modifier of an expression, we have to understand that this expression can only calculate over the data that corresponds to the dimension value in that row , or in the case of a pivot table, that column. For example, the following chart uses the previous expression in a table with Month as a chart dimension and we can see what happens when we select March, as follows:

Accumulated Sales does not accumulate because set analysis is not an inter-row function or, in other words, it does not see data outside the data slice defined by the dimension values. Even if we use the Total keyword to allow the previous expression to calculate overall data, we still don't get the result that we expect because the maximum Month in the set modifier (March) is the same for every value in the Month chart dimension:

The solution in order to create the table at the beginning of this section is to use inter-row functions, such as above() or below(), in combination with range functions, such as rangesum(). We use the following code for Accumulated Sales:

rangesum(before(sum([Net Sales]),0,ColumnNo(Total)+1)) 

We can also use the Accumulation section in the Expressions tab if we use a straight table and only one dimension, or we can use a more robust solution such as the As Of Calendar we used in Chapter 3, Financial Perspective.