11 Replies Latest reply: Jan 8, 2013 4:10 PM by Josh Duenyas

# What to do when "X" field is blank

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])))

• ###### Re: What to do when "X" field is blank

try:

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

• ###### Re: What to do when "X" field is blank

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.

• ###### Re: What to do when "X" field is blank

Try

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

• ###### Re: What to do when "X" field is blank

is it blank or null?

Use this for blank

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

• ###### Re: What to do when "X" field is blank

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.

• ###### Re: What to do when "X" field is blank

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)

• ###### Re: What to do when "X" field is blank

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

• ###### Re: What to do when "X" field is blank

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?

• ###### Re: What to do when "X" field is blank

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)

• ###### Re: What to do when "X" field is blank

Hmm…the calculation below is ignoring the bill date column altogether and just calculating sum (total request amount) – sum (reimbursement amount)

• ###### Re: What to do when "X" field is blank

I appologize.

You want to sum only if date EXISTS. Is that true? (your original post states this : if(Len(trim([Bill Date]))>0)

If so then use this

if(NOT isnull([Bill Date]) AND len(Trim([Bill Date])) <> 0, sum([Total Request Amount]-if(not isnull([Reimbursement Amount]),[Reimbursement Amount],0)))