1 Reply Latest reply: Jun 25, 2018 9:45 AM by Barbara Livieri RSS

    Division in QlikSense over same set of countries

    Barbara Livieri



      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:

      Measure_1Germany, UK, Denmark, Sweden, USis_Internal = 1


      is_Internal = 0
      Measure_3UKis_Internal = 0

      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?