Showing Missing Time Data in QV
by Speros Kokenes, on Jan 2, 2014 10:28:23 AM
A common problem I encounter in QlikView is missing data in trend charts. Take the following table for example:
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:
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:
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:
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:
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:
In this set analysis expression, we limit the IsoMonthYear to start and end at the variables vMinMonthYear and vMaxMonthYear. vMinMonthYear is defined as:
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:
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.