17 Replies Latest reply: Jul 12, 2011 12:28 PM by Nicolas MARTIN RSS

    Using "if" in an expression : how to have distinct values ?

      Good day,


      I have the following data :



      LOAD * INLINE [
      Country, City, Value
      France, Paris, 1
      France, Lyon, 2
      USA, Washington, 4
      USA, New York, 8
      Japan, Tokyo, 16


      I'm using a separate table to manage geography :



      Country AS Country_dim,
      City AS City_dim
      RESIDENT Data;




      When I do a simple graph using the "Country" dimension, I have the following correct result :


      with a "sum(Value)" formula.



      When I do more complex graphics, I need to use the "Country_dim" dimension as graph dimension (instead of "Country"), using a "if" in my expression


      sum(if(Country_dim = Country, Value))


      Because "Country_dim" have a frequency > 1, I have the wrong values :




      The solution I found is to make a division :


      sum(if(Country_dim = Country, Value))


      but my total goes wrong :




      So, I'm using a more complex formula as workaround :


      if(dimensionality() = 1,
                sum(if(Country_dim = Country, Value))
                if(dimensionality() = 0,


      that gives me the expected result :






      BUT, this workaround can give me very very complex formulas when I have to use many dimensions, and this formulas can be very very long to compute (because the "if" make a calculation of every pieces of formula).



      Is there a way to say "I want to make my test on the distinct values of Country_dim" ?



      I hoped that "sum(if(only(Country_dim) = Country, Value))" would work, but it doesn't.