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



      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):



      LOAD * INLINE [
          exe.TIPO, exe.ID, req.ID, exe.N
          EXE,1, 2, 1
      LOAD * INLINE [
          req.TIPO, req.ID, req.N
          REQ, 1,1
      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