Selecting aggregate data from Qlik DataMarket

Some Qlik DataMarket data sets contain dimensions that include an aggregate field value. These dimensions represent hierarchies and contain more than one field, representing levels of that hierarchy. Fields within the hierarchy may contain blank values. The numbers associated with those blank values are aggregates of the numbers associated with the other values in that field.

For example, the World population by country data set contains a Sex dimension. It has the subset field values of Female and Male. When Sex is loaded into Qlik Sense, it contains three values: Female, Male, and a blank value for the aggregate field value.

Bar chart with the dimension Sex, the total male and female population for Argentina is displayed as two separate bars with an unnamed bar for the aggregate total.

In the example displayed in the image, the aggregate field value contains the total of both Female and Male.

The blank value field is included whenever all subset field values of a dimension are included. The totals associated to the aggregate field values are present in the data and can result in double-counting if they are including when calculating aggregates over data.

Depending on your visualizations used, you can exclude the aggregate field values. Fox example:

  • Set the blank value to null using the Set nulls data profiling card in Data manager and exclude the null value from the dimension by clearing Include null values in the Dimension section of Properties.
  • Use an expression to limit what dimension values are included and then clear Include null values in the Dimension section of Properties. For example, from the Sex dimension, you could use the expression =if(match(Sex,'Female','Male'),Sex) to exclude the aggregate field value.
  • Use Set analysis expressions to exclude the blank value's aggregate numbers from a measure.
Note: When working with data sets such as World population by country that contain multiple fields with aggregate data, ensure that the tables with those aggregate fields are not directly associated. If they are directly associated, they are likely to create a circular reference.

For more information, see Understanding and solving circular references.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?