filtering on any value

[Anonymous]

Hi Guys,

I have question ,

I want to filter the shipments that are late, but are not caused by our company. I have a comment field in qlikview wherin we describe the what the reason of delay is, e.g.  'problems with customs in Beijing' or 'Dammage goods'. If the field is empty, then the delay is our fault.

Now, I want to translate this in my gauge chart, my expression below is incorrect:

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime, 1,0) and [PO NO.: & Comments]>0) / Count(DISTINCT  OrderNo)

How can I filter on comments that contain any text value?

Thanks in advance!

Isam

[swuehl · Accepted solution]

Maybe you want to check the length of the field value:

and len([PO NO.: & Comments])>0)

If you could have spaces in that fields, you may want to apply an additional trim() first:

and len(trim([PO NO.: & Comments]))>0)

But I think your if-statement is somewhat incorrect (the and is not part of the if-condition), not sure, but maybe like this

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime and len(trim([PO NO.: & Comments]))>0 , 1,0)

/ Count(DISTINCT  OrderNo)

Hope this helps,

Stefan

[Anonymous]

Hi Stefan,

Again thanks you for your help!! I really appreciate it .  I've attached my qlikview file.

The expression does not seem to work. I get a performance of 68.400% 

Hope u can help.

Regards,

iSam

[swuehl]

Where do you get the number 68.400% from?

If I put above expression (after changing to field name Comments and adding one closing bracket after the sum), I get something like 12%. (No selections made).

[Anonymous]

Hi stefan,

This is what I get when using this expression:

sum(if(NetWorkDays(Date,ArrivalDate)<=TransitTime and len(trim(Comments))>0, 1,0))

sorry for all the nagging

thanks in advance!

isam

[swuehl]

Sure, don't you need to divide by something, like count(distinct OrderNo) ?

[Anonymous]

You abosolutly right. I can't believe that I missed the last part of the expression. I was so focused with your expression, that I forgot to add the last part

My apologize for overlooking the last part of the script!

Thanks alot for your help! I really really really appreciate it.

Regards,

Isam