The data model of the Happiness application is not very complicated:
This is an extremely simple data model that only contains one table of real data, Happy Planet Index, and an additional table listing all countries, World.shp/Features. The second table has one record per country and holds the map information—the shapes of the country—used in the map object in the user interface.
In this app, the data table has exactly one record per country—a record that contains the relevant information for a given country at a given moment. However, this is not always the situation. More often, the data table contains data for countries over many points in time, for example, one record per combination of a country and a year. This will result in several lines per country.
There are not many fields that can be used as dimensions. The three available fields are region, subregion, and country. The world is split into 7 regions and 19 subregions. A country can only belong to one subregion and one region. These fields have been added to Library. In addition, a drill-down dimension has been created from the three fields.
The dimensions in Library
One way of adding dimensions could be by creating buckets based on one of the measures, for example, population. Countries could then be grouped under Large, Medium, and Small classes, which will be stored in a new field, Population Class.
A number of measures have also been defined, for example, GDP, happiness index, global footprint, life expectancy, and so on.
It is important that the app developer formulates the formulas correctly, since this is something that could be difficult for the business user. The business user doesn't always have knowledge about the data model, which is something you need in order to get all the expressions right.
In the following table, you can find some of the measures defined in this app:
|
Measure |
Definition |
|---|---|
|
GDP per Capita |
|
|
Global Footprint |
|
|
Governance Rank |
|
|
Happy Life Years |
|
|
Happy Planet Index |
|
|
HPI Rank |
|
|
Population |
|
Several of these measures can be defined differently. How you do this is very much a matter of taste. For instance, the measures where the Only() function is used can also be defined using Sum() or Avg(). As long as you only have a single number, all three functions will return the same answer.
But how do you want Qlik Sense to behave when there are several countries, for example, a region that should be represented by one value? For the Population measure, the obvious function to use should be Sum(). Then the total population of the region will be shown.
But if the source data contains several years, so that a single country has several records, you don't just want to sum the population. Then you would get numbers that are much larger than they should be. Instead, you might want to use Sum(Population)/Count(distinct Year) to create an average over all possible years.
In addition, for a rank, you wouldn't want to use Sum() because it would show an incorrect number. You could use Avg(), which will give the average rank between the countries. An average is clearly better, but it is still not mathematically correct. Then it might be better to use Only(), which doesn't return an answer at all when more than one country is involved.