Sum Null Values

[hopkinsc]

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?

[swuehl · Accepted solution]

Try

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

[hopkinsc]

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?

[hopkinsc]

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

[swuehl]

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.