topic Sum Null Values in QlikView

Sum Null Values

hopkinsc — Fri, 01 Nov 2013 14:50:36 GMT

Hi, i was wondering how i could sum null vales of a field.

Eg.

if i wanted to

sum(Sales)

but i want to add set analysis to include a field [Transaction Type] where [Transaction Type] is null.

I have tried ..

if(isnull([Transaction Type]), sum(Sales))

but this gives me the same result as Sum(Sales)

Can anyone offer any help please?

Re: Sum Null Values

swuehl — Fri, 01 Nov 2013 14:55:35 GMT

Try

sum( if(isnull([Transaction Type]), Sales))

Re: Sum Null Values

hopkinsc — Fri, 01 Nov 2013 15:19:40 GMT

Hi,

Thanks thats what i wanted..

I now have the following 2 expressions..

sum({<[To Group]={'Other'}>}[Net Invoice Line Value Total])

sum({<[Transaction Code]={'CRN','STD','SEC'}>} if(isnull([To Group]), [Net Invoice Line Value Total]))

I want to combine them into one expression, could you help me combine them please?

Re: Sum Null Values

hopkinsc — Fri, 01 Nov 2013 15:22:29 GMT

hang on, am i being stupid? is it as easy as adding them both together?

Re: Sum Null Values

swuehl — Fri, 01 Nov 2013 15:26:20 GMT

The obvious solution would be

=sum({<[To Group]={'Other'}>}[Net Invoice Line Value Total])

sum({<[Transaction Code]={'CRN','STD','SEC'}>} if(isnull([To Group]), [Net Invoice Line Value Total]))

I assume you want only one sum(). This is not so easy to answer, the if() function is evaluated per row, while the set expression filter on the distinct field values. Besides that, you can't select a NULL value (in field To Group).

Might sound confusing, all I want to say is that the solution probably depends on your data model or might involve changes to your data model. So if possible, post a small app that represents your model, including some sample data and your expected outcome.