Showing Missing Time Data in QV




A common problem I encounter in QlikView is missing data in trend charts. Take the following table for example:

Division MonthYear Sales
A Nov 2013 100
B Nov 2013 50
A Dec 2013 123
B Dec 2013 43
A Jan 2014 109
A Feb 2014 84
B Feb 2014 60
A Mar 2014 99
B Mar 2014 58

Division B did not record any sales for Jan 2014. Therefore, a row was never entered in the table. If we try to plot this data in QlikView as a line chart, we will see the following:

Multiple Lines

 

QlikView cannot find a data point for Division B, MonthYear Jan 2014, so it simply does not draw any point. The result is that it connects the next available points, producing a misleading chart. If I had not drawn the symbols in this chart, it would appear that Division B had upwards trending sales through the months of December to February. The chart becomes even more obviously wrong if I filter to just Division B:

Filtered to B

 

Because "Jan 2014" is not in the available data set, it is not plotted on the x-axis. This is a blatant problem to users, who understand MonthYears as a continuous range of values.

So how do we correctly plot this data without modifying it to include "0" sales rows for any missing periods? One option is to use a combination of an isolated dimension, set analysis, and an if statement:

 

Iso

An isolated MonthYear dimension is necessary so we don't filter out any points. We create this in the script by just loading the MonthYear values and aliasing them as IsoMonthYear, which has no connection to any other fields in the data model:

Calendar

 

The IsoMonthYear dimension will always contain every value of MonthYear, regardless of what is filtered in the Fact table. Therefore, when Division B is selected, we will still have Jan 2014 available. However, for this example we still want our users to be able to filter the MonthYears by Quarter and Year. This is where the set analysis comes into play:

sum({<IsoMonthYearSort={'>=$(vMinMonthYear)<=$(vMaxMonthYear)'}>}

In this set analysis expression, we limit the IsoMonthYear to start and end at the variables vMinMonthYear and vMaxMonthYear. vMinMonthYear is defined as:

=min({<IsoYear=P(Year),IsoQuarter=P(Quarter)>} IsoMonthYearSort)

In this expression, we use set analysis to filter the isolated calendar table down to the IsoYears and IsoQuarters that are possible in the Fact table's Year and Quarter selections. Then, we find the minimum IsoMonthYearSort.

The last piece of the puzzle is the if statement to correctly align the Fact table data with the IsoMonthYear dimension:

sum({<IsoMonthYearSort={'>=$(vMinMonthYear)<=$(vMaxMonthYear)'}>} if(IsoMonthYear=MonthYear,Sales))

Each "row" of the trend chart will have an IsoMonthYear value. We check this against the MonthYear value in every row of the Fact table and return the Sales values that match. The result when plotting for B is that no sales will be returned for IsoMonthYear Jan-2014, so the sum will be 0.

There are a couple of downsides to this implementation. First, you cannot filter the fact table by selecting within the chart. In order to avoid confusion with the user, it'd be best to set the chart to "Read Only". The second downside is that I expect there to be a negative impact on performance with larger fact tables.

Download the sample QVW here.

-Speros

TAGS: QlikView, Visualization

Related News

IS YOUR COMPANY UPGRADING TO QLIKVIEW 12?

Axis Group was approached by a client asking for help...

Read More

Part 1 of this series describes what Qlik Sense does better than QlikView, from the perspective...

Read More