Filling missing value with number 0

[#1]

Hi guys,

I m creating a report where there are many missing values...for instance...

if you look at first line then there is a missing value for cust4 because there is no row for account "Advertising and Promotion" for cust4.

My requirement is to fill these cells with number 0.

I tried "Populate missing cells" but it has two problems...first it perhaps does not enter a number 0 and second it fills all other misisng cells in different columns also with 0 which is not desirable...

Is there any other way to accomplish my task?

Regards,

Saurabh

[Anonymous]

Ramya

In your expression could you do something like :

     if ( isnull ( [YourField] ) , 0 , sum ( [YourField] )

Best Regards,     Bill

[#3]

i did this

sum(if isnull([Field]), 0, Field)

and it did not work....

[Nicole-Smith · Accepted solution]

alt(YourExpression, 0)

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

[Clever_Anjos]

alt(sum(yourfield),0) and fill Null Symbol and Missing symbol with "0.00"

[#6]

So for my purpose...

it might look like

alt(Not IsNull(Amount), 0)...

I tried but it gave some weird results....positive amounts became negative...

let me try few more options and i will update you.

[Nicole-Smith]

alt(Amount, 0)

[#8]

let me try this.

[#9]

nope, it is not working...

but let me try few other options of expressions...

[Anonymous]

Ramya

The alt() that has been suggested by other above worked fine when I tested it, see attached qvw.

Best Regards,     Bill