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

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

    Nicolas MARTIN

      Good day,

       

      I have the following data :

       

       

      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 :

       

       

      Geography:
      LOAD DISTINCT 
      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 :

      2011-07-12_122355.png

      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 :

      2011-07-12_122630.png

       

       

      The solution I found is to make a division :

       

      sum(if(Country_dim = Country, Value))
      /
      count(Country_dim)
      
      

       

      but my total goes wrong :

      2011-07-12_122819.png

       

       

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

       

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

       

      that gives me the expected result :

       

      2011-07-12_123038.png

       

       

       

      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.