2 Replies Latest reply: May 10, 2012 1:44 AM by Filippo Tabusso RSS

    strange behaviour of sum with if condition on null values inside

      Hi,

       

      I have the following problem: I want to sum all values of a field where some associated dimensions are null. Here is my data script (very simplified version):

       

       

      exe:
      LOAD * INLINE [
          exe.TIPO, exe.ID, req.ID, exe.N
          EXE,1, 2, 1
      ];
      
      
      req:
      LOAD * INLINE [
          req.TIPO, req.ID, req.N
          REQ, 1,1
      ];
      
      
      ans:
      LOAD * INLINE [
          ans.TIPO, req.ID
          ANS, 1
      ];
      
      

       

      just to summarize: I have requests (req) which can be executed or not (exe) and have answers (ans). I want to sum req.N for requests not executed (exe.TIPO=null) but answered (ans.TIPO<>null)

       

      Here is my expression: sum(if(len(exe.TIPO)=0 and len(ans.TIPO)>0,req.N))

       

      if I put it within a table chart I get 2 instead of one!

       

      Does anyone has some answers? What am I doing wrong?

       

      Thank you in advance for all your help