Integrating corporate data with DataMarket data

Qlik DataMarket data can be integrated with your corporate data to provide a broader context for that data and yield greater insights into your markets.

DataMarket data comes from a variety of sources, and as a result, associations with your data may not always be immediately evident. You might find that a number of associations have to be edited in the data preparation step. For example, you might find it valuable to evaluate certain characteristics of the countries you operate in. But fields for countries in some DataMarket data sets might not have enough values in common with your corporate data to make the association useful. That is why you must carefully assess the associations between your data and DataMarket data.

The following illustrations demonstrate how to integrate corporate and DataMarket data and create meaningful Qlik Sense visualizations.

Compare sales to Gross Domestic Product (GDP)

When you are doing business in a country or considering entering a country's market, there are a number of indicators that can help you determine if your company's performance aligns as you expect with the demographic and economic environment in that country.

The corporate data in this illustration enables sales data to be aggregated by country. A bar chart compares the sales by country. That can provide insight into how your company is doing across all its markets.

To see how you are doing within each country, you could compare your company's sales to country data that indicates how strong the market is. For example, you could compare sales in each country with the country's Gross Domestic Product (GDP). Or you could compare sales to the demographics of your target market. If your company's target is people ages 21 to 35 year, you can see how many people the countries have in that age category. Or see what percentage of the total population is in that age category.

Qlik DataMarket contains a data set in the Essentials Free group called Select development indicators that provides a number of economic measures, including GDP growth rates, literacy, internet users, total population, and GDP per capita in US dollars. To associate country data from Select development indicators, the corporate data must have a field that matches the Country field in the DataMarket data set. If the corporate data has many more countries than the Select development indicators, the association would not be strong and probably not useful. If the corporate data has fewer countries than the Select development indicators, the association can probably be useful for a comparison.

Assuming there is a good association between the country fields in the corporate and DataMarket data, you can add GDP per capita in US dollars to the sales bar chart to compare the sales in each country to GDP.

Show sales in euros

When reviewing sales data, a common visualization is a KPI chart that shows total sales. In the illustration here, the sales total is in US dollars because that is how the corporate data is recorded. With so many sales in countries that use the euro, it might be good to see the sales total in euros. That can be added to the KPI. In the Essentials Free group, DataMarket contains 3x3 currency exchange rates that can be used to convert US dollars to euros.

When selecting the Base currency field in the 3x3 currency exchange rates data set, you would select only US dollars because that is the currency in which sales are recorded. In the corporate data set used in this illustration, there is a field named Base currency that indicates the currency each customer uses. However, it contains twelve different currencies, and as a result, the Data manager recommends against associating the two fields. You should not associate those fields as the currencies in the corporate data that do not correspond to US dollars and euros can interfere with some comparisons of dollars and euros. The data model then appears as follows:

The Quote currency from 3x3 currency exchange rates should be Euro. The DateTime selection should be Most recent because you want only the current exchange rate, not historical data, for the KPI visualization.

To get Euro Sales, you simply multiply the Sum(Sales) by the Exchange rate in the 3x3 currency exchange rates data set.

Create alternative measures for sales

With DataMarket currency data, you can add a further enhancement to the sales bar chart. The chart compares sales to per capita GDP in US dollars for each country, but alternative measures can be added for the sales and GDP. The Sum(Sales) can be multiplied by the Exchange rate in the 3x3 currency exchange rates table to show sales in euros, just as was done in the Sales KPI. The per capita GDP in US dollars can be multiplied by the Exchange rate as well, and then the bar chart compares sales to per capita GDP in euros.

This is where it is important that the Base currency fields not be linked because, as noted above, the corporate data set's Base currency field contains twelve different currencies. When the exchange-rate calculation is performed on the separate countries, the base currency for each country would be used if the tables were linked. But the corporate data does not contain any sales values in most of those twelve currencies. It contains sales values only in US dollars. And the Base currency from the DataMarket data set is only US dollars, so for any country that has a Base currency value other than US dollars in the corporate data, Sales in euros would be a null value if the two tables were linked.