Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

11 Replies
dt_adamwilson
Contributor

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

try:

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

christian77
Valued Contributor

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

Try

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

Not applicable

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.

christian77
Valued Contributor

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

Not applicable

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.

jduenyas
Contributor III

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)

jduenyas
Contributor III

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.

Not applicable

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?

jduenyas
Contributor III

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)