# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Division in QlikSense over same set of countries

Hi,

I'm trying to create a measure which is a division between two different metrics, that can be chosen from a set of possible KPIs by the user. Some of these measures are available in a less countries than others.

Let's say that the data looks something like this:

MeasureCountriesInternal/external
Measure_1Germany, UK, Denmark, Sweden, USis_Internal = 1
Measure_2

UK

is_Internal = 0
Measure_3UKis_Internal = 0
Measure_4

Germany, UK, Denmark, Sweden, US

is_Internal = 1

The data model has all countries for each measure and in case no value is available for that country, the value is set to 0.

Note: I have tried excluding the missing countries for some measures but this solution does not work.

If the division is between Measure_2 and Measure_3 or Measure_1 and Measure_4, there is no problem since the data is available in the same countries, but if I try to divide Measure_2 by Measure_4, the metric stops making sense.

At the moment I have fixed this issue with and IF statement in the measure calculation, but I don't like this solution since it seems very inefficient:

IF (MIN({<[Metric]={'\$(=Denominator)'}>} is_Internal) = 0

or MIN({<[Metric]={'\$(=Numerator)'}>} is_Internal) = 0 ,

sum({\$<[Measure]={'\$(=Numerator)'},Country = P(external_country)>} [Measure_Value])

/

sum( {\$<[Measure]={'\$(=Denominator)'},Country = P(external_country)>} [Measure_Value])

,

sum({\$<[Measure]={'\$(=Numerator)'}>} [Measure_Value])

/

sum( {\$<[Measure]={'\$(=Denominator)'}>}[Measure_Value]))

Any ideas on how I could structure the data model to fix this?

1 Solution

Accepted Solutions
New Contributor II

## Re: Division in QlikSense over same set of countries

Solved the issue in the following way:

sum({\$<[Measure]={'\$(=Numerator)'}, Country =  {"=SUM({<[Measure]={'\$(=Denominator)'}>} [Measure_Value])>0"}>}[Measure_Value])

/

sum({\$<[Measure]={'\$(=Denominator)'}, Country =  {"=SUM({<[Measure]={'\$(=Numerator)'}>} [Measure_Value])>0"}>}[Measure_Value])