05 August 2013

Qlikview: Missing data points due to data reduction



If data reduction is enabled, the data available in the qlikview application is filtered according to the user logging in. For example, if the logging in user has no "sales" data associated with him/her for "Product A", "Product A" will be filtered out for this user.

This may make sense in most of the cases, however same thing exists for "date" data too. If the logging in user has no "sales" data for "July 2012", then once logged in, (s)he will not have "July 2012" data point in his/her "sales per month" chart. This may not be what is expected; expected chart behavior might be to have a "0" value data point for "July 2012".

A possible workaround for this issue is as follows:

1) Since data reduction is filtering out data, we need to prevent data reduction in "date" data. This can be achieved by the STAR function of qlikview (Pls see the script in the sample qlikview app). In the sample qlikview app, I preferred to create a duplicate of DateDim table in which data reduction is prevented, that way I can use the regular DateDim table as usual and the duplicate table (DateDim_ALL) for recuperating the filtered out "date" data.

In the sample qlikview application, please login as "User1" and observe that "CalendarMonth" field is missing "200802" and "200803" but  "CalendarMonth_ALL" field does not.

2) Create a chart with "CalendarMonth_ALL" as dimension and observe that missing dates are still missing :(.

3) Add a hidden expression (as first expression) that counts the "CalendarMonth_ALL" between the MAX and MIN dates available in fact table.

4) Set dimension limit to show "greater than zero", as dimension limit is applied to first expression, it is applied to the expression mentioned in the step above.

5) Make the chart read-only, as selections made on "CalendarMonth_ALL" won't be reflected across the application.

Please see the bottom chart in the sample application as an example. Try logging in with "User1", "User2" and "User3".

Here is the link to download the sample application: missing.qvw



3 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete