8 Replies Latest reply: Sep 25, 2014 6:48 AM by anbu cheliyan

how to use isnull in expression

Hi,

how to use isnull in expression?

• Re: how to use isnull in expression

IsNull(expr )

Returns -1 (true) if expr returns NULL, otherwise 0 (false).

Use IsNull(FieldName) will return true if it is null

• Re: how to use isnull in expression

What are you trying to achieve.

You can use like this

If(RowNo() = 0 or IsNull(RowNo()),Sum(Sales)

regards

ASHFAQ

• Re: how to use isnull in expression

In set analysis How to write isnull

eg.I want to use isnull for profitCent,But How to use in this expression.

num(sum({<PODate = {'>=\$(=Date(MonthStart(min(CalenderDate)))) <=\$(=Date(max(CalenderDate)))'} >} Amt))

• Re: how to use isnull in expression

Hi

Considering profitCent is valid field.

Try like below

num(sum({<PODate = {'>=\$(=Date(MonthStart(min(CalenderDate)))) <=\$(=Date(max(CalenderDate)))'},profitCent={''}>} Amt))

Regards

ASHFAQ

• Re: how to use isnull in expression

How to use isnull in given example

• Re: how to use isnull in expression

No Attachment

regards

ASHFAQ

• Re: how to use isnull in expression

Try

num(sum({<PODate = {'>=\$(=Date(MonthStart(min(CalenderDate)))) <=\$(=Date(max(CalenderDate)))'},

Amt={'=Len(Trim(profitCent))=0'}>} Amt))

Or

num(sum({<PODate = {'>=\$(=Date(MonthStart(min(CalenderDate)))) <=\$(=Date(max(CalenderDate)))'},

Amt={'=NullCount(profitCent)>0'}>} Amt))

• Re: how to use isnull in expression

Amol,

Below are few examples:

1. IF(ISNULL(ItemName)=0, ItemName, '001' ) AS ItemName

2.If( Len(Trim(Field))=0 or Isnull(Field) , this, that) as FieldName

3. =If( if(Year<>2013,avg([Act % of Rev.]),(Sum([Act. Scrap \$])/sum([Act Revenue])))<>0,

if(Year<>2013,avg([Act % of Rev.]),(Sum([Act. Scrap \$])/sum([Act Revenue]))),

null()

)

Thanks,

AS