Skip to main content Skip to complementary content

Accumulating values over a dimension in a table

This example shows how to use a table to compare data which accumulate over a dimension.

Table comparing data accumulated over a dimension

Dataset

In this example, we will use a data file available in the Qlik Sense Tutorial - Building an App. Download and expand the tutorial. The file is available in the Tutorials source folder: Sales.xls

To download the file, go to Tutorial - Building an App.

Add the data file to an empty app. The dataset that is loaded contains sales data.

Measure

We use the sales volume as the measure that we create in Master items:

  • Sales with the expression Sum(Sales). This is the sum of the sales volume.

Visualization

We add a table to the sheet and set the following data properties:

  • Dimension: Year (Date.Year).
  • Dimension: Month (Date.Month).
  • Measure: Sales; the measure that was previously created.

The following table is created, with columns showing the year, the month, and the sum of sales for each month.

Table showing the year, the month, and the sum of sales for each month

Make sure to set the Sorting with Year > Month > Sales.

Accumulation

To have our sales data accumulate over one dimension, we need to set an additional data property:

  • Measure: Sales; the measure that was previously created.

We add this measure twice to reuse it for accumulation. To do this we need to apply a modifier to our Sales measure.

  1. Click Advanced options.

  2. In the properties panel, under Measure: Sales set the Modifier to Accumulation. This will set the measure to accumulate over one dimension.
  3. Set the Modifier>Dimension to Month. This sets the dimension over which the accumulation will take place.
  4. Make sure Across all dimensions is turned off. We want the accumulation to restart at the beginning of each year.

Information noteIn Accumulation there is the option to select Show excluded values. When turned on, your visualization includes any dimensional values that do not have data. This ensures that all values, including months with no sales data, are counted in the accumulation.

Our table becomes as follows, with the last column showing sales accumulating from one month to the next, for each year.

table with sales accumulating from one month to the next, for each year.

It is good practice to have the title of your charts represent their content. So consider changing the titles of the columns of your table to reflect that the last column contains an accumulation of sales.

Our data for the year 2014 is insufficient as it stops at June. In order to achieve a better comparison between the accumulated sales of all three years we will choose a more appropriate range of months, from January to June.

  1. Click Done.
  2. Select all months from January to June and confirm your selection.

Our table becomes as follows, with the sales accumulating from January to June, inside each year.

Table comparing data accumulated over a dimension

Discovery

The table shows the sales volume of each month, grouped into years. By having the sales accumulate inside each year, we get a better understanding of the volume of sales for each year. We have made a selection of the months from January to June, to compare the same range of months between the three years. In the last column of the table we can see that the accumulated sales for 2014 so far have been higher than the accumulated sales for 2012, but not as high as the ones for 2013.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!