20 June 2012

Qlikview: Calculated Dimensions 2: Cumulative Sum


If we have a gap in our data, then Full Accumulation mode (in expressions tab of chart properties) may not work as we like.

For example, if we have sales data for a country over 12 months and if there is no sales data for August, we will have a chart which is missing August, as follows:

And we will also miss the data for the country (in this example it is Turkey) in a Full Accumulation mode, please see the August bar in the following chart:

Normally, since there is no data for August, we would expect to see the same value in August as July, in a cumulative sum chart.




To resolve this, we can do the followings:

  1. Use calculated dimensions for 12 moths. Such a calculated dimension ensures that we have 12 months displayed in the chart
    vMonths='Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'

    calculated dimension =valuelist($(=vMonths))
  2. Deactivate suppress zero values option in the presentation tab of chart properties
  3. Activate Full Accumulation mode in the expressions tab
Please see the August bar in the resulting chart below, now it display data for Turkey in August bar:


Here is the qlikview file:


No comments:

Post a Comment