Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

barbarali
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
barbarali
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])

1 Reply
barbarali
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])