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

    Division in QlikSense over same set of countries

    Barbara Livieri

      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?