QlikView Zero Line Fix

If you have ever been bothered by QlikView suppressing line chart time data points for unassociated values (instead of making the line(s) go down to zero, this article will describe a sophisticated workaround that will not require you to concatenate every possible data combination to your fact table!

A problem that plagues many QlikView implementations, especially those in the Pharma industry, is line chart behavior when your data set has been reduced such that the selected dimensions do not have values for all line chart time dimension values. For instance, suppose that you have a line chart that displays total prescriptions (TRx) per drug for the past 13 weeks—a very common requirement in Pharma applications. When you first open your application, your line chart may look as follows:
 
 
While this may seem correct, if you are reading this article you probably already suspect what will happen when a single prescriber is selected:

 
 
The problem is that most prescribers do not have activity on all Products and/or Territories every single week. When a doctor like this is selected, he/she has no data for that Product-Week or Territory-Week combination. Because the Fact table is used as a sort of "key hub" in a proper star-schema data model, this means that there is no key correlation for those data points. QlikView, being an associative data modeling tool, simply "skips over" this data point. Meaning, if a prescriber has activity in weeks 1 & 3, but no activity in week 2, QlikView will draw a line between the 2 data points on weeks 1 and 3 and skip over week 2 in such a way that it appears to not even exist. This can be dangerous as users can draw incorrect conclusions from such a line chart. For example, using the screenshot above, it appears that this prescriber has a steady upwards trend for Drug A from weeks 10/15/2012 through 10/29/2012, whereas his activity for that drug actually dipped to zero on 10/22/2012 (note circled areas).
 
One way to fix this problem would be to simply concatenate onto the Fact table every possible combination of Prescribers, Products, and Territories with a value of '0' for TRx. However, a quick calculation reveals that this would often require adding hundreds of millions or even billions of rows to the Fact table to cover every combination. The methods described in this article will show you how to fix the above issue, with only a minimal impact on the number of rows in the Fact table.
 
Data Model Modifications
 
The only concatenation you need to implement is to ensure that the dimensions that you are trying to view graphically themselves (i.e. Product and Territory, in our example) have a match for each date key. Meaning, instead of creating all combinations with each Prescriber, you simply need to concatenate all Product-Week and Territory-Week combinations. To put this in perspective, suppose that you have 50 products, 500 territories, 50,000 prescribers, and 2 years of data (104 weeks). Trying to create every combination (including on Prescribers) would result in 50 * 500 * 50,000 * 104 = 130 billion added rows to your fact table, something that is clearly not practical. However, simply creating 2 concatenations of Product-Week and Territory-Week combinations would result in 50 * 104 + 500 * 104 = 57,200 added rows to your Fact table. We will also poll the Fact table to ensure that we do not concatenate records where a combinations already exists, thus even further reducing the impact of concatenation. To see an example of how to accomplish this concatenation, download the QVW attached at the end of this post. A summary of the modification is also included below:
 

//Loading all existing combinations to ensure that unnecessary rows are not concatenated & creating temporary table (which will be smaller than real-world fact table)
fact_temp:
LOAD DISTINCT
      DATE_KEY,
      PRODUCT_ID,
      TERRITORY_ID,
      DATE_KEY & '|' & PRODUCT_ID as date_prod_exists,
      DATE_KEY & '|' & TERRITORY_ID as date_terr_exists
RESIDENT Fact;

dates1:
LOAD DISTINCT DATE_KEY RESIDENT fact_temp;

dates2:
NOCONCATENATE LOAD DISTINCT DATE_KEY RESIDENT fact_temp;

JOIN (dates1) LOAD DISTINCT PRODUCT_ID RESIDENT fact_temp; //Cartesian join to create all combinations of dates and products

CONCATENATE (Fact) LOAD //adding those date-product combinations that do not already exist in Fact
      *,
      0 as TRx
RESIDENT dates1
WHERE NOT exists(date_prod_exists,DATE_KEY & '|' & PRODUCT_ID)
;

JOIN (dates2) LOAD DISTINCT TERRITORY_ID RESIDENT fact_temp; //Cartesian join to create all combinations of dates and products

CONCATENATE (Fact) LOAD //adding those date-territory combinations that do not already exist in Fact
      *,
      0 as TRx
RESIDENT dates2
WHERE NOT exists(date_terr_exists,DATE_KEY & '|' & TERRITORY_ID)
;

DROP TABLES dates1,dates2;

 
UI Revisions - Method 1
 
The first method of revising the UI is the simplest, but requires that your second chart dimension be assigned a static color in the data model. For example, often Pharma implementations will assign a static color to a each Product in the data set so that color can be used repeatedly and consistently. For line charts that use such a dimension as the second dimension in a line chart, the Zero Line fix may be implemented as follows:
 
  • Create a flag in your Calendar table to indicate the relevant weeks that you want to view. In our example, this field is called #Last13WeeksFlag and will have a value of '1' for the most current 13 weeks of data.
  • Assuming your Product coloring field is called ProductColor, set your chart expression background expression to the following: only({1<#Last13WeeksFlag={1},Product=P({<#Last13WeeksFlag={1}>}Product)>} ProductColor)
  • Under Presentation, uncheck the "Suppress Zero Values" checkbox.
 
Explanation: This fix will cause QlikView to create a value for the expression's Background Color for each data point, while ignoring all user selections (such as the Prescriber selection that caused the zero-line problem in the first place). It will, however, only display data for the relevant timeframe (13 weeks in our case) and relevant Products (using the set analysis P() function). It will also only ignore user selections for coloring purposes, but will respect selections (through the chart expression itself) for all other purposes.
 
UI Revisions - Method 2
 
Method #1 is not appropriate for those charts that do not use fixed dimensional coloring for the second line chart dimension. The following Zero Line fix is intended for such charts. Please note that, for performance reasons, Method #1 is slightly preferable to Method #2, so should be used whenever possible.
 
  • If you have not already done so in Method #1 above, create a flag in your Calendar table to indicate the relevant weeks that you want to view. In our example, this field is called #Last13WeeksFlag and will have a value of '1' for the most current 13 weeks of data.
  • Assuming your chart expression is something like sum({<#Last13WeeksFlag={1}>} TRx) modify the expression to the following:

    if(sum({<#Last13WeeksFlag={1}>} TRx)>0,
         sum({<#Last13WeeksFlag={1}>} TRx),
         sum({1<#Last13WeeksFlag={1},Territory=P({<#Last13WeeksFlag={1}>}Territory)>} 1) - sum({1<#Last13WeeksFlag={1},Territory=P({<#Last13WeeksFlag={1}>}Territory)>} 1)
    )

  • Under Presentation, uncheck the "Suppress Zero Values" checkbox.
 
Explanation: This fix is quite interesting because it actually involves the creation of a new type of "zero"! The IF statement in the modified expression will display the value of the expression as-is when a non-zero TRx value is detected. However, if the value of the expression is not greater than zero (including nulls) the ELSE portion of the IF statement will subtract an expression from itself to create a zero values in place of the previous null values.
 
Results
 
Applying Method #1 to the top chart in our example, and Method #2 to the bottom chart, the following is a screenshot of how the updated charts appear. Note that the data points that were previously missing now correctly show as '0' in both charts:
 
 
To see the solutions discussed in this article in detail, feel free to download the following QVW: ZeroLineFix.qvw
 
This entry was posted in Development and tagged , , . Bookmark the permalink.

7 Responses to QlikView Zero Line Fix

  1. Brian says:

    I often come across this problem and this is great example of how to overcome it. That being said….dear God I wish it were simpler.

    Good job Vlad, thanks!

  2. Mili says:

    Excellent work, congratulations and so many thanks Vlad!

  3. Bea says:

    Hi, the qvw download isn’t working. Is it possible to reload it please? Also, can I get away with reducing concatenations if I need the line chart to be selectable on multiple combinations (such as region, country, type etc)? Or would I need a combination for every user selection available?

  4. Hi, Bea. The download is working fine on my end–give it another try (possibly from a different network, if you’re on a laptop).

    Regarding the concatenations, I suggest you re-read the article, particularly the “Data Model Modifications” section. The advantage of the solution described here is that you only need to ensure that you have at least 1 Fact record for each combination of date key and dimension(s) that you are trying to view graphically (e.g. Product and Territory). You certainly don’t need to create unique combinations for region/country/type, unless those are in fact the dimensions of the line graph.

    Regards,
    Vlad

  5. Jon of All Trades says:

    This is a good workaround, but a better fix here would be to use a more appropriate visualization: a column chart.

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify via email when new comments are added

Blog Home
Archives