What to do when "X" field is blank

[#1]

I'm trying to write an expression that says when the Bill Date is blank, calculate the difference in the sums of the Total Request Amount and Reimbursement Amount. Unfortunately, what I'm using below isn't working. Any ideas? Thanks in advance.

=money(sum(if(Len(trim([Bill Date]))>0, [Total Request Amount]-[Reimbursement Amount])))

[adamwilson]

try:

=money(sum(if(Len(trim([Bill Date])) = 0, [Total Request Amount]-[Reimbursement Amount])))

[christian77]

Try

if(isnull([Bill Date]), sum([Total Request Amount]-[Reimbursement Amount]))

[#4]

Hmm…that does not seem to be working. When the Bill Date field is blank, the Total Request Amount sums to 655k and the Reimbursement Amount sums to 84k. This formula is giving me 19k as a result instead of 571k.

[christian77]

is it blank or null?

Use this for blank

sum({$<[Bill Date]={' '}>}[Total Request Amount]-[Reimbursement Amount]))

[#6]

It is blank.

I want to find the total in the Total Request Amount column and subtract it from the total in the Reimbursement Amount column. For some reason this is giving me a zero answer.

[jduenyas]

Try this:

if(isnull([Bill Date]) or len(Trim([Bill Date])) = 0, sum([Total Request Amount]-[Reimbursement Amount]))

When a field is NULL you cannot get its 'length' nor can you Trim it.

Verify that the field in the database is not set to Allow 0 length (or Allow NULL)

[jduenyas]

Also make sure that the fields ([Total Request Amount] and [Reimbursement Amount] are not NULL in the calculation because results may ne skewed.

[#9]

Ah – that’s the problem! The Total Request Amount has a corresponding amount in every field but the Reimbursement Amount does not have numbers in most of the fields. Any way to get around that?

[jduenyas]

Yes:

if(isnull([Bill Date]) or len(Trim([Bill Date])) = 0, sum([Total Request Amount]-if(not isnull([Reimbursement Amount]),[Reimbursement Amount],0)))

(Microsoft's ACCESS has a nifty function

VARIABLE=NZ(FiledName,Value)   -- The function is the NZ()

in which if the FieldName is NULL then the VARIABLE will get the value of Value. If it is not NULL then VARIABLE will get the FieldName's value. It can be a string or numeric.

Unfortunaelty it is not available in QlikView's VB Scripts)