Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
Ramya
In your expression could you do something like :
if ( isnull ( [YourField] ) , 0 , sum ( [YourField] )
Best Regards, Bill
i did this
sum(if isnull([Field]), 0, Field)
and it did not work....
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).
alt(sum(yourfield),0) and fill Null Symbol and Missing symbol with "0.00"
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.
alt(Amount, 0)
let me try this.
nope, it is not working...
but let me try few other options of expressions...
Ramya
The alt() that has been suggested by other above worked fine when I tested it, see attached qvw.
Best Regards, Bill