topic strange behaviour of sum with if condition on null values inside in QlikView

strange behaviour of sum with if condition on null values inside

Wed, 09 May 2012 17:09:34 GMT

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

Re: strange behaviour of sum with if condition on null values inside

chematos — Wed, 09 May 2012 18:26:10 GMT

The first condition is false, len(exe.TIPO) is 3.

try this:

if(len(exe.TIPO)=0 and len(ans.TIPO)>0,sum(req.N))

Regards,

Chema

strange behaviour of sum with if condition on null values inside

Thu, 10 May 2012 05:44:09 GMT

Hi Josè, thank you for your reply.

the first condition is false for req.ID=2 but for req.ID=1 it is true since there is no execution for that request.

I've tried to put the sum within the if as you suggested, but it only works if i put one of the *.TIPO fields as dimensions. This is not what I want since I have to plot the result of my conditional sum over a time dimension (not represented in the simplified data I posted.

Best regards,

Filippo